Help with loop code please

[blackie42]

Hi

Following code creates the formula in S10 and loops down column S - issue being it doesn't stop at the last used row.


With Range("S10", Range("S" & Rows.Count).End(xlDown))
.FormulaR1C1 = "=RC[-4]*RC[-11]"
.NumberFormat = "#,##0.00"
End With


Can be achieved with a Do Loop & offset but its very slow


Range("S10").Select
Do
ActiveCell.FormulaR1C1 = "=RC[-4]*RC[-11]"

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Offset(0, -3).Value = ""


Missing something on the first code

Any help appreciated

thanks
Jon

[Leith Ross]

Hello Jon,

Try this...


With Range("S10", Cells(rows.Count, "S").End(xlUp))
.FormulaR1C1 = "=RC[-4]*RC[-11]"
.NumberFormat = "#,##0.00"
End With


Note if S10 is empty then the first cell could be from S1 to S9.

To be sure your first cell is S10 use this...


Dim RngBeg As Range
Dim RngEnd As Range

Set RngBeg = Range("S10")
Set RngEnd = Cells(Rows.Count, "S").End(xlUp)

If RngEnd.Row < RngBeg.Row Then Set RngEnd = RngBeg

With Range(RngBEg, RngEnd)
.FormulaR1C1 = "=RC[-4]*RC[-11]"
.NumberFormat = "#,##0.00

End With

[blackie42]

thanks for trying but doesn't work

[blackie42]

thanks for trying but only populates S10

[Paul_Hossler]

thanks for trying but only populates S10

Attach a workbook with the before and after to make it easier to see what you want to do

[blackie42]

Test file attached

thanks

[blackie42]

sorry - correct test file - copies formula up

[mdmackillop]

Just offset from a known range



With Range(Cells(10, 8), Cells(10, 8).End(xlDown)).Offset(, 11)
.FormulaR1C1 = "=RC[-4]*RC[-11]"
.NumberFormat = "#,##0.00"
End With

[blackie42]

Sub test()
Lastrow = Range("H" & Rows.Count).End(xlUp).Row
Range("S10:S" & Lastrow).Formula = "=RC[-4]*RC[-11]"
End Sub

[SamT]

Sub Blakie42_FillFormulas()
Dim LR As Long
With Sheets("Sheet1")
LR = .Range("H10").End(xlDown).Row
With Range(.Cells(10, "S"), .Cells(LR, "S"))
.FormulaR1C1 = "=RC[-4]*RC[-11]"
.NumberFormat = "#,##0.00"
End With
End With
End Sub

[blackie42]

Thanks for everones time.

regards
Jon

[SamT]

We couldn't solve your problem, hunh?

[blackie42]

Hi Sam,

I solved it myself in my last post - which was similar to your reply.

I was just thanking everyone who had posted replies

Just being polite

regards
Jon