PDA

View Full Version : dynamic formula help



Gtrain
04-09-2008, 12:03 AM
ok team awesome,

looks like i new some assistance again

i am trying to dynamically update a formula based on the last row returned from an sql query, but i keep getting run time error 438
object doesn't suppot this property or method.

Any thoughts

Sub brform()
Dim wkrs As Worksheet
Dim Lastrn
Dim LastRowNumber As String
Set wkrs = ActiveWorkbook.Worksheets("BR KPI Report")
Set Lastrn = wkrs.Range("a65536").End(xlUp)

LastRowNumber = Lastrn.Row + 1
wkrs.Range("j3").Forumla = "=SUMIF(H2:H" & LastRowNumber & ","">0"")/COUNTIF(H2:H" & LastRowNumber & ", "">0"")"
End Sub

Gtrain
04-09-2008, 12:05 AM
i was just trying the as string to see if made any difference, it didnt!!

Gtrain
04-09-2008, 12:32 AM
Fixed it, just thought i would drop it for anyone else!


Sub brform()
Dim wkrs As Worksheet
Dim Lastrn
Dim LastRowNumber As Integer
Set wkrs = ActiveWorkbook.Worksheets("BR KPI Report")
Set Lastrn = wkrs.Range("a65536").End(xlUp)

LastRowNumber = Lastrn.Row + 1
wkrs.Cells(3, 10).Value = "=SUMIF(H2:H" & LastRowNumber & ","">0"")/COUNTIF(H2:H" & LastRowNumber & ", "">0"")"
End Sub

Aussiebear
04-09-2008, 02:11 AM
There you go Gtrain... well done.:hifive:

mdmackillop
04-09-2008, 04:17 AM
Hi Gtrain
FYI


Set Lastrn = wkrs.Range("a65536").End(xlUp).Offset(1)

will give you the next row for adding new data

rory
04-09-2008, 05:16 AM
Also, if you use a variable for row numbers, you should really make it a Long not an Integer in cae you go over 32767 rows.

Gtrain
04-15-2008, 07:51 PM
Thanks for the extra feedback, i will make changes to my code.

G