0
Sub example()

Dim LastRow As Integer
LastRow = (ActiveSheet.UsedRange.Rows.Count)

R = 6
Do Until R = LastRow
If (Range("Y" & R) > 0) Then
Range("AZ" & R) = "Weighted ave formulae"
Range("AG" & R).Formula = "=(U1*W1)"
End If
R = R + 1
Loop

End Sub

Where U1 and W1 should always be U and the current Rth row and W and the current Rth row

I need the AG to contain the formula itself and not just the result

Zqm
  • 11
  • 2
  • 2
    Skip the loop: `Range("AG6:AG" & lastrow).formula = "=U6*W6"` – Rory Oct 12 '22 at 08:49
  • You can construct the formula string using CStr as follows. "=(U" & cstr(R) & "*W" & cstr(R) & ")". – freeflow Oct 12 '22 at 08:49
  • 1
    **1.** Avoid using `Integer` when working with rows. Use `Long`. I have explained it in [Find last used cell in Excel VBA](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba). I have also demostrated on how to find the last row there. **2.** I agree with @Rory's suggestion. Much easier to fill the range without using the loop. It is much faster. Similarly `Range("AZ6:AZ" & lastrow).Value= "Weighted ave formulae"` – Siddharth Rout Oct 12 '22 at 08:58
  • A few (side)notes: `LastRow = ActiveSheet.UsedRange.Rows.Count` (the parentheses are redundant) will only work if the first row is not empty. More accurate is e.g. `LastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1`. Use `Option Explicit` which forces you to declare all variables (`Dim R As Long` is missing). The logic `Do Until R = LastRow` excludes the last row; it should e.g. be `Do Until R > LastRow`. The formulas could e.g. be `"=U" & R & "*W" & R`; again, no need for the parentheses. – VBasic2008 Oct 12 '22 at 10:12

0 Answers0