Recording Macro - Excel makes reference to a specific cell
 

Hi. I have recorded my first excel Macro. I am trying to copy/ paste
a cell down
for as many rows that are in my file. However, as shown in the
following code, it is making reference to cell B42, which happens to
be the end of the file (i.e. End-Down) that I used to record the
macro. It causes
my macro to not work properly if there are a different amount of rows
in my file. Each file I use will have a different amount of rows.

Does anyone know what sequence of actions I should use when recording
the macro -- to prevent specific cells from being referenced?

Or. can someone who understands Visual Basic take a stab at
correcting my code so that it will work with any amount of rows.
Thanks in advance.

Sub TestMacro()
Range("B2").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"
Range("B2").Select
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
Range("B42").Select
Range(Selection, Selection.End(xlUp)).Select
Range("B3:B42").Select
Range("B42").Activate
ActiveSheet.Paste
End Sub

Recording Macro - Excel makes reference to a specific cell
 

You probably have to change the "b" in the first line to a column with the
number of rows desired for the formulas in colB.

Sub copyfomrula1()
lr = Cells(Rows.Count, "b").End(xlUp).Row

Range("B2").FormulaR1C1 = "=UPPER(RC[-1])"
With Range("b2:b" & lr)
..FillDown
..Value = .Value 'comment out if you need the formulas
End With
end sub

--
Don Guillett
SalesAid Software

"FamilyGuy902" wrote in message
ups.com...
Hi. I have recorded my first excel Macro. I am trying to copy/ paste
a cell down
for as many rows that are in my file. However, as shown in the
following code, it is making reference to cell B42, which happens to
be the end of the file (i.e. End-Down) that I used to record the
macro. It causes
my macro to not work properly if there are a different amount of rows
in my file. Each file I use will have a different amount of rows.

Does anyone know what sequence of actions I should use when recording
the macro -- to prevent specific cells from being referenced?

Or. can someone who understands Visual Basic take a stab at
correcting my code so that it will work with any amount of rows.
Thanks in advance.

Sub TestMacro()
Range("B2").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"
Range("B2").Select
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
Range("B42").Select
Range(Selection, Selection.End(xlUp)).Select
Range("B3:B42").Select
Range("B42").Activate
ActiveSheet.Paste
End Sub

Recording Macro - Excel makes reference to a specific cell
 

Give this a try:
Sub TestMacro()
Dim BtmRow As Long
BtmRow = Cells(65536, "a").End(xlUp).Row
Range("B2:B" & CStr(BtmRow)).FormulaR1C1 = "=UPPER(RC[-1])"
End Sub
James
FamilyGuy902 wrote:
Hi. I have recorded my first excel Macro. I am trying to copy/ paste
a cell down
for as many rows that are in my file. However, as shown in the
following code, it is making reference to cell B42, which happens to
be the end of the file (i.e. End-Down) that I used to record the
macro. It causes
my macro to not work properly if there are a different amount of rows
in my file. Each file I use will have a different amount of rows.

Does anyone know what sequence of actions I should use when recording
the macro -- to prevent specific cells from being referenced?

Or. can someone who understands Visual Basic take a stab at
correcting my code so that it will work with any amount of rows.
Thanks in advance.

Sub TestMacro()
Range("B2").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"
Range("B2").Select
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
Range("B42").Select
Range(Selection, Selection.End(xlUp)).Select
Range("B3:B42").Select
Range("B42").Activate
ActiveSheet.Paste
End Sub

Recording Macro - Excel makes reference to a specific cell
 

Thanks James! Worked like a charm!

Jason


Zone wrote:
Give this a try:
Sub TestMacro()
Dim BtmRow As Long
BtmRow = Cells(65536, "a").End(xlUp).Row
Range("B2:B" & CStr(BtmRow)).FormulaR1C1 = "=UPPER(RC[-1])"
End Sub
James
FamilyGuy902 wrote:
Hi. I have recorded my first excel Macro. I am trying to copy/ paste
a cell down
for as many rows that are in my file. However, as shown in the
following code, it is making reference to cell B42, which happens to
be the end of the file (i.e. End-Down) that I used to record the
macro. It causes
my macro to not work properly if there are a different amount of rows
in my file. Each file I use will have a different amount of rows.

Does anyone know what sequence of actions I should use when recording
the macro -- to prevent specific cells from being referenced?

Or. can someone who understands Visual Basic take a stab at
correcting my code so that it will work with any amount of rows.
Thanks in advance.

Sub TestMacro()
Range("B2").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"
Range("B2").Select
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
Range("B42").Select
Range(Selection, Selection.End(xlUp)).Select
Range("B3:B42").Select
Range("B42").Activate
ActiveSheet.Paste
End Sub

Recording Macro - Excel makes reference to a specific cell
 

You're welcome, Guy. Thanks for letting me know! James
FamilyGuy902 wrote:
Thanks James! Worked like a charm!

Jason


Zone wrote:
Give this a try:
Sub TestMacro()
Dim BtmRow As Long
BtmRow = Cells(65536, "a").End(xlUp).Row
Range("B2:B" & CStr(BtmRow)).FormulaR1C1 = "=UPPER(RC[-1])"
End Sub
James
FamilyGuy902 wrote:
Hi. I have recorded my first excel Macro. I am trying to copy/ paste
a cell down
for as many rows that are in my file. However, as shown in the
following code, it is making reference to cell B42, which happens to
be the end of the file (i.e. End-Down) that I used to record the
macro. It causes
my macro to not work properly if there are a different amount of rows
in my file. Each file I use will have a different amount of rows.

Does anyone know what sequence of actions I should use when recording
the macro -- to prevent specific cells from being referenced?

Or. can someone who understands Visual Basic take a stab at
correcting my code so that it will work with any amount of rows.
Thanks in advance.

Sub TestMacro()
Range("B2").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"
Range("B2").Select
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
Range("B42").Select
Range(Selection, Selection.End(xlUp)).Select
Range("B3:B42").Select
Range("B42").Activate
ActiveSheet.Paste
End Sub