I need to use the last row of data in a formula
in [Programming]
|
From: vero on 3 Jun 2010 11:59 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
From: Jacob Skaria on 3 Jun 2010 12:10 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
From: Mike H on 3 Jun 2010 12:10 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
From: vero on 3 Jun 2010 12:24 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
|
Pages: 1 Prev: Copy and paste to other worksheets Next: Subject: Re: Updateing Access DB |