Recording Macro - Excel makes reference to a specific cell

[FamilyGuy902]

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

 

[Don Guillett]

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

 

[Zone]

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

 

[FamilyGuy902]

Thanks James! Worked like a charm!

Jason

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

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

 

[Zone]

You're welcome, Guy. Thanks for letting me know! James

Thanks James! Worked like a charm!

Jason

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

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