Crashed and burned

[swaink]

Hi All

I have been trying without success to apply a formula using VBA to a range on a single row.

I want to use VBA to add the following formula starting at column N, I intend to use lastrow = range("A65535").end(xlup).row and use that result as part of the formula build to cater for increasing rows, I also intend to find the last column in the range.

=SUM(IF(ISNUMBER(N6:N207),N6:N207,0)/100*$I$6:$I$207)

When the formula is reflected across the row the last part of the formula would anchor to column I.

However I just can't seem to get this to work and wonder if someone could assist me in solving this problem please?

All advice appreciated

Regards

Kevin

 

[VoG]

Which row should the formula be entered in?

 

[swaink]

Hi Peter

Thanks for the reply

I'm looking to offset the row from the lastrow, in this example it would be in row 210

Kev

 

[VoG]

Try like this

Sub Test2()
Dim LR As Long, LC As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Range(Cells(LR + 1, 14), Cells(LR + 1, LC)).Formula = "=SUM(IF(ISNUMBER(N6:N" & LR & "),N6:N" & LR & ",0)/100*$I$6:$I$" & LR & ")"
End Sub

 

[swaink]

Hi Peter

Not only have you solved the problem yet again for me and I thank you..

But you have also taught me a new approach to finding the last row and last column too

Your help is appreciated

Kevin