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
Search'!$G$3:$G$90)),1,1)

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

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)"


--
Jacob (MVP - Excel)


"vero" wrote:

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
Search'!$G$3:$G$90)),1,1)

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

Hi,

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)"

--
Mike

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


"vero" wrote:

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
Search'!$G$3:$G$90)),1,1)

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

Thank you Jacob & Mike, it works perfect!

"vero" wrote:

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
Search'!$G$3:$G$90)),1,1)

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