paste formula every 4th row dependin on value in Col A

[r333dma]

I’m an occasional user of VBA so knowledge very limited.
I have worksheet columns A-G starting at row 4, cell H7contains a formula based on D4 and D7. I need some VBA which identifies whenCol A has a value in A11, A15, etc (every 4^th row) and then copiesthe formula from H7 to every 4^th row in Col H. Each time the formulais pasted it needs to look at Col D (current row) and Col D (current row -3). Ifthere is no value in Col A then nothing would happen.
Any help with this would be appreciated.

 

[ranman256]

you didnt provide the formula.

Public Sub MakeFormulae()
Dim iRows As Long
Dim vFormu


vFormu = "R[-1]+c[2]"


Range("A4").Select
iRows = UsedRange.Rows.Count


For r = 4 To iRows Step 4
   ActiveCell.Offset(4, 0).Select
      'check col H
   If ActiveCell.Value <> "" Then ActiveCell.Offset(0, 7).FormulaR1C1 = "=" & vFormu
Next
End Sub

 

[r333dma]

Hi Ranman256

I have added the code but get a run time error object required at iRows = UsedRange.Rows.Count I added my formula which is D7-D4 and still got the error.

any ideas.

regards

Mark

 

[Fluff]

How about

Sub AddFormula()
   Dim i As Long
   
   For i = 11 To Range("A:G").find("*", , xlValues, , xlRows, xlPrevious, False, , False).Row Step 4
      If Range("A" & i) <> "" Then Range("H" & i).FormulaR1C1 = "=rc4-r[-3]c4"
   Next
End Sub

 

[r333dma]

thanks Fluff, does exactly what I wanted.

cheers

Mark

 

[Fluff]

Glad to help & thanks for the feedback