CODEWITHSUNDEEP
vbaexcel
DGMS89
DGMS89

Reputation: 1677

Finding the Last Row and using it in a formula

I am dealing with a sheet of data that has multiple rows ans columns. Each time the macro runs, the number of rows can be different, so I am trying to find the last row for a column.

With the last row, I am trying to do a calculation. For example: if the row I get is 1200, I can do A1200/A2-1. MY code should explicitly paste the formula in an output worksheet and currently (currently I have to put the last row myself).

Question: How can I get the last row and put it in a formula? Should I assign it to a variable and then use the variable in the formula?

Lines of code I am using:

Sub Output()
Dim LastRowA As Long

LastRowA = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

'this is my current method, it works using specific cells.
'I would like to change the D1662, for example, for a floating reference that gets the last row

Worksheets("Sheet2").Range("C2:C2").Formula = "='TIME SERIES'!D1662/'TIME SERIES'!D2-1"

End Sub

Upvotes: 0

Views: 3912

Answers (2)

Blake
Blake

Reputation: 230

How about a user defined function? This way you can add it to your equation in excel with =LastRow(ColumnNumber). You can leave this in the vba editor and in excel set you cell value to "=LastRowValue("SheetName",1)/A2-1" where 1 would be column A.

Function LastRowValue(WorksheetName As String, Col As Long) As Long
'=======================================================================
'This can be typed into an excel cell as a normal function
'"=LastRow("SheetName",ColumnNumber)" The Column Number is indexed 
'starting with A=1 so C=3 and AA=27. Enter the WorksheetName in
'quotes "WorksheetName".
'========================================================================
Dim LR As Long

'LR will find the last row in column number "col" in WorksheetName.
LR = ThisWorkbook.Sheets(WorksheetName).Cells(Rows.Count, Col).End(xlUp).Row

'LastRowValue will be the output of this function and will be the value of 
'the last row in WorksheetName.
LastRowValue = Cells(LR, Col).Value

End Function

Upvotes: 1

SJR
SJR

Reputation: 23081

Like so. Just remove the variable from the quotes.

Sub Output()

Dim LastRowA As Long

LastRowA = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

'this is my current method, it works using specific cells.
'I would like to change the D1662, for example, for a floating reference that gets the last row

Worksheets("Sheet2").Range("C2:C2").Formula = "='TIME SERIES'!D" & LastRowA & "/'TIME SERIES'!D2-1"

End Sub

Upvotes: 2

Related Questions