PDA

View Full Version : [SOLVED] Help with loop code please



blackie42
06-30-2017, 10:33 AM
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
06-30-2017, 10:50 AM
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
06-30-2017, 03:02 PM
thanks for trying but doesn't work

blackie42
06-30-2017, 03:05 PM
thanks for trying but only populates S10

Paul_Hossler
06-30-2017, 03:12 PM
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
07-01-2017, 07:55 AM
Test file attached

thanks

blackie42
07-01-2017, 08:02 AM
sorry - correct test file - copies formula up

mdmackillop
07-01-2017, 08:07 AM
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
07-01-2017, 08:09 AM
Sub test()
Lastrow = Range("H" & Rows.Count).End(xlUp).Row
Range("S10:S" & Lastrow).Formula = "=RC[-4]*RC[-11]"
End Sub

SamT
07-01-2017, 08:27 AM
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
07-02-2017, 01:23 PM
Thanks for everones time.

regards
Jon

SamT
07-02-2017, 01:54 PM
We couldn't solve your problem, hunh?

blackie42
07-03-2017, 10:28 AM
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