I need to use the last row of data in a formula



I'm writing a macro to calculate the coefficient and intercept of a log curve

the following is what the equation should look like in the cell:
=INDEX(LINEST('Expect Database Search'!$Q$3:$Q$90,LN('Expect Database

however in VB I have to write it in this format:
Range("B2").Formula = "=INDEX(LINEST('Expect Database
Search'!R3C17:R90C17,LN('Expect Database Search'!R3C7:R90C7)),1,1)"

My problem is that the data set is never the same so I may have 100 rows or
1000 or anything in between. Is there a way to write this so that the
formula will find the last row of data and use that row number instead?

I've found examples of how to find the last row and the following is what I
have used in my code:
Dim LastRow As Long
Sheets("Expect Database Search").Select
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

but how do I now use this is my formula?

thank you for any insight you may provide



Jacob Skaria

Dim lngRow As Long
lngRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("B2").Formula = "=INDEX(LINEST('Expect Database Search'!$Q$3:$Q$" & _
lngRow & ",LN('Expect Database Search'!$G$3:$G$" & lngRow & ")),1,1)"

Mike H


Maybe this way

Dim LastRow As Long
Set sht = Sheets("Expect Database Search")

LastRow = sht.Cells(Rows.Count, "Q").End(xlUp).Row
sht.Range("B2").Formula = _
"=INDEX(LINEST('Expect Database Search'!$Q$3:$Q$" & _
LastRow & ",LN('Expect Database Search'!$G$3:$G$" & LastRow & ")),1,1)"


When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question