Use of New Range Names in Macro

  • Thread starter Thread starter Pete W
  • Start date Start date
P

Pete W

Can anyone help me on this one?

In my macro below I am tring to set a Range Name
("NewMonth1") to the first active cell that is inserted
into the spreadsheet. But what I am finding is that when
you create a range name while recording a macro it
references that specific cell in a absolute reference. How
do I make thw macro look at this as a relitive reference
for future use?

Sub AddMonths()
'
' Add Macro
' Macro recorded 10/21/2003 by Pete Wright
'
Counter = "counter"
Value = "value"
Application.ScreenUpdating = False

MS:

Value = InputBox("How many Colums do you want to
add?", "Add Colums")
If range("Value") = Empty Then
MsgBox prompt:="You did not enter a Value."
GoTo MS
End If

Do Until Value = 0
ActiveCell.Offset(0, 1).Columns
("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(0, 0).range("A1").Select
ActiveWorkbook.Names.Add Name:="NewMonth1",
RefersToR1C1:="=Sheet1!R1C22"
Value = Value - 1
Loop
range("Month").Value = InputBox("What month do you
want to start with?", "Input Month")
range("Month_In").Select
ActiveCell.FormulaR1C1 = range("Month")

Value = InputBox("How many months do you want to
add?", "Add Months")
If range("Value") = Empty Then
MsgBox prompt:="You did not enter a Value."
GoTo MS
End If
Do Until Value = 2
ActiveCell.Offset(0, 1).Columns
("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(1, 0).range("A1").Select
Value = Value - 1
Loop

range("Month_In").Select
Selection.AutoFill Destination:=range
("Month_In:Month_Out"), Type:=xlFillDefault
range("Month_In:Month_Out").Select
Selection.Copy
range("Mpaste").Select
Selection.PasteSpecial Paste:=xlValues
range("Monthin_FormDol").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2]C,dollar)"
range("Monthin_FormLBS").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-3]C,_LBS)"
range("Monthin_FormDol:Monthin_FormLBS").Select
Selection.AutoFill Destination:=range
("Monthin_FormLBS:end"), Type:=xlFillDefault
range("Monthin_FormDol:Monthin_FormLBS").Select

****************************************************
After the above macro is preformed I have to run the next
macro to actually insert the correct mounth in place.
Please not that in the above macro where the range name is
inserted it references ActiveWorkbook.Names.Add
="NewMonth1", RefersToR1C1:="=Sheet1!R1C22" and down below
when I find the range and delete it, I then move over 2
spaces and re-insert the same range name for future use.
This will work for only 2 months, but what do I do if I
need to add 3-11 months worth of info.
****************************************************

End Sub
Sub AddMonthstoColums()
'
' Add Macro
' Macro recorded 10/27/2003 by Pete Wright
'

Selection.Copy
range("MMpaste1").Select
Selection.PasteSpecial Paste:=xlValues
range("MonthDol:MonthLBS").Select
Selection.Copy
ActiveWindow.ScrollColumn = 10
range("NewMonth1").Select
Selection.PasteSpecial Paste:=xlValues
ActiveWorkbook.Names("NewMonth1").Delete
ActiveCell.Offset(0, 2).range("A1").Select
ActiveWorkbook.Names.Add Name:="NewMonth1",
RefersToR1C1:="=Sheet1!R1C24"
ActiveWindow.LargeScroll ToRight:=2
range("Month").Select
End Sub

I know it got to be easier than what I can think of.
Thanks in advance.
Pete W
 
Since I don't know what your range names represent, it's hard to tel
exactly what you're doing (i.e, it looks like you're adding columns
twice - once as columns and once as "months", right?). In any case,
this may work for you. I detest Goto's so I recast your input as a
loop and put some rudimentary error checking in:

Dim result As Variant
Do
result = Application.InputBox( _
Prompt:="How many columns do you want to add?", _
Title:="Add Columns", _
Default:=0, _
Type:=1)
If result = False Then Exit Sub '(user clicked Cancel)
Loop Until result > 0
With ActiveCell
.Offset(0, 1).Resize(1, result).EntireColumn.Insert
.Offset(0, 1).Name = "NewMonth1"
End With

Note that no selections are needed - this inserts "result" columns
in one step.

Pete W said:
Can anyone help me on this one?

In my macro below I am tring to set a Range Name
("NewMonth1") to the first active cell that is inserted
into the spreadsheet. But what I am finding is that when
you create a range name while recording a macro it
references that specific cell in a absolute reference. How
do I make thw macro look at this as a relitive reference
for future use?

Sub AddMonths()
'
' Add Macro
' Macro recorded 10/21/2003 by Pete Wright
'
Counter = "counter"
Value = "value"
Application.ScreenUpdating = False

MS:

Value = InputBox("How many Colums do you want to
add?", "Add Colums")
If range("Value") = Empty Then
MsgBox prompt:="You did not enter a Value."
GoTo MS
End If

Do Until Value = 0
ActiveCell.Offset(0, 1).Columns
("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(0, 0).range("A1").Select
ActiveWorkbook.Names.Add Name:="NewMonth1",
RefersToR1C1:="=Sheet1!R1C22"
Value = Value - 1
Loop
range("Month").Value = InputBox("What month do you
want to start with?", "Input Month")
range("Month_In").Select
ActiveCell.FormulaR1C1 = range("Month")

Value = InputBox("How many months do you want to
add?", "Add Months")
If range("Value") = Empty Then
MsgBox prompt:="You did not enter a Value."
GoTo MS
End If
Do Until Value = 2
ActiveCell.Offset(0, 1).Columns
("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(1, 0).range("A1").Select
Value = Value - 1
Loop

range("Month_In").Select
Selection.AutoFill Destination:=range
("Month_In:Month_Out"), Type:=xlFillDefault
range("Month_In:Month_Out").Select
Selection.Copy
range("Mpaste").Select
Selection.PasteSpecial Paste:=xlValues
range("Monthin_FormDol").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2]C,dollar)"
range("Monthin_FormLBS").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-3]C,_LBS)"
range("Monthin_FormDol:Monthin_FormLBS").Select
Selection.AutoFill Destination:=range
("Monthin_FormLBS:end"), Type:=xlFillDefault
range("Monthin_FormDol:Monthin_FormLBS").Select

****************************************************
After the above macro is preformed I have to run the next
macro to actually insert the correct mounth in place.
Please not that in the above macro where the range name is
inserted it references ActiveWorkbook.Names.Add
="NewMonth1", RefersToR1C1:="=Sheet1!R1C22" and down below
when I find the range and delete it, I then move over 2
spaces and re-insert the same range name for future use.
This will work for only 2 months, but what do I do if I
need to add 3-11 months worth of info.
****************************************************

End Sub
Sub AddMonthstoColums()
'
' Add Macro
' Macro recorded 10/27/2003 by Pete Wright
'

Selection.Copy
range("MMpaste1").Select
Selection.PasteSpecial Paste:=xlValues
range("MonthDol:MonthLBS").Select
Selection.Copy
ActiveWindow.ScrollColumn = 10
range("NewMonth1").Select
Selection.PasteSpecial Paste:=xlValues
ActiveWorkbook.Names("NewMonth1").Delete
ActiveCell.Offset(0, 2).range("A1").Select
ActiveWorkbook.Names.Add Name:="NewMonth1",
RefersToR1C1:="=Sheet1!R1C24"
ActiveWindow.LargeScroll ToRight:=2
range("Month").Select
End Sub

I know it got to be easier than what I can think of.
Thanks in advance.
Pete W
Click to expand...
 
J.E.

You are correct that I am addind two seperate sets of
colums in this macro. Unfortunatly the Dim statment below
will only enter one colum to the spreadsheet and will not
name the cell? any other ideas, otherwise thanks for your
help.

Pete W
-----Original Message-----
Since I don't know what your range names represent, it's hard to tel
exactly what you're doing (i.e, it looks like you're adding columns
twice - once as columns and once as "months", right?). In any case,
this may work for you. I detest Goto's so I recast your input as a
loop and put some rudimentary error checking in:

Dim result As Variant
Do
result = Application.InputBox( _
Prompt:="How many columns do you want to add?", _
Title:="Add Columns", _
Default:=0, _
Type:=1)
If result = False Then Exit Sub '(user clicked Cancel)
Loop Until result > 0
With ActiveCell
.Offset(0, 1).Resize(1, result).EntireColumn.Insert
.Offset(0, 1).Name = "NewMonth1"
End With

Note that no selections are needed - this inserts "result" columns
in one step.

Pete W said:
Can anyone help me on this one?

In my macro below I am tring to set a Range Name
("NewMonth1") to the first active cell that is inserted
into the spreadsheet. But what I am finding is that when
you create a range name while recording a macro it
references that specific cell in a absolute reference. How
do I make thw macro look at this as a relitive reference
for future use?

Sub AddMonths()
'
' Add Macro
' Macro recorded 10/21/2003 by Pete Wright
'
Counter = "counter"
Value = "value"
Application.ScreenUpdating = False

MS:

Value = InputBox("How many Colums do you want to
add?", "Add Colums")
If range("Value") = Empty Then
MsgBox prompt:="You did not enter a Value."
GoTo MS
End If

Do Until Value = 0
ActiveCell.Offset(0, 1).Columns
("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(0, 0).range("A1").Select
ActiveWorkbook.Names.Add Name:="NewMonth1",
RefersToR1C1:="=Sheet1!R1C22"
Value = Value - 1
Loop
range("Month").Value = InputBox("What month do you
want to start with?", "Input Month")
range("Month_In").Select
ActiveCell.FormulaR1C1 = range("Month")

Value = InputBox("How many months do you want to
add?", "Add Months")
If range("Value") = Empty Then
MsgBox prompt:="You did not enter a Value."
GoTo MS
End If
Do Until Value = 2
ActiveCell.Offset(0, 1).Columns
("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(1, 0).range("A1").Select
Value = Value - 1
Loop

range("Month_In").Select
Selection.AutoFill Destination:=range
("Month_In:Month_Out"), Type:=xlFillDefault
range("Month_In:Month_Out").Select
Selection.Copy
range("Mpaste").Select
Selection.PasteSpecial Paste:=xlValues
range("Monthin_FormDol").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2] C,dollar)"
range("Monthin_FormLBS").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-3]C,_LBS)"
range("Monthin_FormDol:Monthin_FormLBS").Select
Selection.AutoFill Destination:=range
("Monthin_FormLBS:end"), Type:=xlFillDefault
range("Monthin_FormDol:Monthin_FormLBS").Select

****************************************************
After the above macro is preformed I have to run the next
macro to actually insert the correct mounth in place.
Please not that in the above macro where the range name is
inserted it references ActiveWorkbook.Names.Add
="NewMonth1", RefersToR1C1:="=Sheet1!R1C22" and down below
when I find the range and delete it, I then move over 2
spaces and re-insert the same range name for future use.
This will work for only 2 months, but what do I do if I
need to add 3-11 months worth of info.
****************************************************

End Sub
Sub AddMonthstoColums()
'
' Add Macro
' Macro recorded 10/27/2003 by Pete Wright
'

Selection.Copy
range("MMpaste1").Select
Selection.PasteSpecial Paste:=xlValues
range("MonthDol:MonthLBS").Select
Selection.Copy
ActiveWindow.ScrollColumn = 10
range("NewMonth1").Select
Selection.PasteSpecial Paste:=xlValues
ActiveWorkbook.Names("NewMonth1").Delete
ActiveCell.Offset(0, 2).range("A1").Select
ActiveWorkbook.Names.Add Name:="NewMonth1",
RefersToR1C1:="=Sheet1!R1C24"
ActiveWindow.LargeScroll ToRight:=2
range("Month").Select
End Sub

I know it got to be easier than what I can think of.
Thanks in advance.
Pete W
Click to expand...
.
Click to expand...
 
I don't follow: the code I posted will add as many columns as the
user enters in the Inputbox, then names the cell to the right of the
activecell "NewMonth1".
 
Back
Top