Copy Formula to last row Via Macro

[neb255]

hi,

i have the following Macro which is supposed to insert a formula in H2, I2, & J2 and copy it down to the last row of column H, I, & J which have data in column A.

it places the formula in the correct cell, however then instead of copying the formula to the last row of column H, I & J it copys it into Cells H1, I1, J1

i need to be able to do this on several sheets where the number of rows of data in column A are not always going to be the same



'Capture Last Row
Dim lastrowa As Long
lastrowa = ActiveSheet.Range("a" & Rows.Count).End(xlUp).Row

'Add Columns Todays Status, Update, & Reg Notes
Sheets("OldData").Select
Range("H1:J1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Range("H1").Select
ActiveCell.FormulaR1C1 = "Todays Status"
Range("I1").Select
ActiveCell.FormulaR1C1 = "UPDATE"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Reg Notes"
Columns("H:J").EntireColumn.AutoFit

'Check Todays Reg
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-2],RegData!C[-4]:C[-3],2,FALSE)),""DEGRADED"",""OPERATIONAL"")"
Selection.AutoFill Destination:=Range("H2:H" & lastrowa)

'Update Date
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-7]=RC[-1],RC[-5],TODAY())"
Selection.AutoFill Destination:=Range("I2:I" & lastrowa)
Columns("I:I").Select
Selection.NumberFormat = "m/d/yyyy"

'Update Reg Status
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]=RC[-2],RC[-3],CONCATENATE(RC[-3],"" "",TEXT(RC[-6],""mm/dd/yyyy"")))"
Selection.AutoFill Destination:=Range("J2:J" & lastrowa)


i appreciate your help!!!

 

[p45cal]

See comments in snippet below:

Sub RegUpdate()
Dim lastrowb As Long
[COLOR=Green]'lastrowb = Range("b" & Rows.Count).End(xlDown).End(xlUp).Row 'your original code.[/COLOR]
lastrowb = Range("b" & Rows.Count).End(xlUp).Row [COLOR=Green]'you don't need End(xldown) in any of these three statements.[/COLOR]
Dim lastrowa As Long
lastrowa = Range("a" & Rows.Count).End(xlDown).End(xlUp).Row
Dim lastrowc As Long
lastrowc = Range("c" & Rows.Count).End(xlDown).End(xlUp).Row
[COLOR=Green]'The values in lastrowa lastrowb  & lastrowc will all be the values of the last cell IN THE ACTIVE SHEET AT THE TIME you start the macro.
'They are all whole numbers.
'If there is no data in the top row of the column in question the value will be 1 (as it will also be if there is only data in the topmost cell of that column (row 1)).[/COLOR]


Stop


[COLOR=Green]'Unhide OldData[/COLOR]
Sheets("OldData").Visible = True

[COLOR=Green]'Pull Client ID From Reg Data[/COLOR]
Sheets("RegData").Select[COLOR=Green] 'Here you select another(?) sheet which becomes the ACTIVESHEET[/COLOR]
Range("A1").Value = "WTN"
Range("B1").Value = "Details"
Range("A2:A" & lastrowa).Select[COLOR=Green] 'here, if lastrowa is 1 then this line becomes
'Range("A2:A1").Select
'which is the same as Range("A1:A2").Select[/COLOR]
Selection.ClearContents
Range("a2:a" & lastrowb).FormulaR1C1 = "=VALUE(MID(RC[1],25,10))" [COLOR=Green]'here, if lastrowb is 1 then this line becomes:
'Range("a2:a1").FormulaR1C1 = "=VALUE(MID(RC[1],25,10))"[/COLOR]

'Refresh Pivot Table
Dim PT As PivotTable
For Each PT In ActiveSheet.PivotTables
    PT.RefreshTable
Next PT

[COLOR=Green]'Clear OldData[/COLOR]
Sheets("OldData").Select
Cells.Select
Selection.Delete [COLOR=Green]' Shift:=xlUp not needed.[/COLOR]

[COLOR=Green]'Copy Yesterdays Data[/COLOR]
Sheets("Active").Select
Range("A:E,L:L,R:R").Select
Selection.Copy
Sheets("OldData").Select
ActiveSheet.Paste


[COLOR=Green]'Add Columns Todays Status, Update, & Reg Notes[/COLOR]
Sheets("OldData").Select
Range("H1:J1").Select
Selection.Font.Bold = True
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 5296274
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

Range("H1").Value = "Todays Status"
Range("I1").Value = "UPDATE"
Range("J1").Value = "Reg Notes"
Columns("H:J").EntireColumn.AutoFit

[COLOR=Green]'Check Todays Reg[/COLOR]
Range("H2:H" & lastrowa).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],RegData!C[-4]:C[-3],2,FALSE)),""DEGRADED"",""OPERATIONAL"")" [COLOR=Green]'again, here if lastrowa has a value of 1 the line starts:
'Range("H2:H1").FormulaR1C1…
[/COLOR] 

[COLOR=Green]'Update Date[/COLOR]
With Range("I2:I" & lastrowa)[COLOR=Green] 'again, here if lastrowa has a value of 1 the line becomes:
'With Range("I2:I1")
'OK you get the picture.[/COLOR]
    .FormulaR1C1 = "=IF(RC[-7]=RC[-1],RC[-5],TODAY())"
    .NumberFormat = "m/d/yyyy"
End With
[COLOR=Green]'
'[snip]
'[/COLOR]
End Sub

So you can see that values in lastrowa/b/c are completely dependent on which sheet happens to be the active sheet at the time you start the macro.
If any of the three column A,B,C have no data in them or just a header in row 1. then the corresponding variable will have a value of 1
I've put a Stop instruction above, after lastrowa/b/c have been assigned values. This pauses the code at that point and will allow you to see what values they hold. Alt+V+S will bring up the Locals Pane which will show the values too, or hover over the variable name in the code to see the value.
Instead of the Stop line you could have these three:

Debug.assert lastrowa >1
Debug.assert lastrowb >1
Debug.assert lastrowc >1

which will not stop the code unless one of those variables is equal to 1.

The solution is either to make sure the right sheet is active when you start the macro, or specify in the code which sheet lastrowa/b/c should be determined from (Select it, or use a With construct with a named sheet:

Dim lastrowb As Long, lastrowa As Long, lastrowc As Long
With Sheets("TheSheetNameInQuestion") 'adjust, obviously.
    lastrowb = .Range("b" & .Rows.Count).End(xlUp).Row    'you don't need End(xldown) in any of these three statements.
    lastrowa = .Range("a" & .Rows.Count).End(xlUp).Row
    lastrowc = .Range("c" & .Rows.Count).End(xlUp).Row
End With

)

 

[p45cal]

i think i know what the issue is. im trying to run the macro from a shape i assigned to the macro, whenever i click on the shape i get the error, but if i run it from the macro's menu it works fine.

any idea why it would be doing that?

Any bets the shape is not on the sheet from which you want to determine lastrowa/b/c?
My previous post stands as the solution.

 

[neb255]

my code now looks like this, and im using a specific "lastrow" for each page....it seems to be working....

and my button works now also!!!

thanks for all your help!!!


'Capture Last Row
Dim lastrowa As Long
Dim lastrowb As Long
Dim lastrowc As Long
Dim lastrowd As Long
Dim lastrowe As Long


lastrowa = Sheets("RegData").Range("b" & Rows.Count).End(xlUp).Row
lastrowb = Sheets("Active").Range("a" & Rows.Count).End(xlUp).Row
lastrowc = Sheets("SN").Range("c" & Rows.Count).End(xlUp).Row
lastrowd = Sheets("NMS").Range("b" & Rows.Count).End(xlUp).Row
lastrowe = Sheets("OldData").Range("a" & Rows.Count).End(xlUp).Row