Hi. I have some code that loops through all cells in column A, and if a
cell is not blank, it inserts a Sumif formula. But I can't figure out how
to code the Criteria part of the formula to be Column A, Row whatever row
the loop is currently on. So in the code below, I need to replace $A11
within the formula with A & current row in the loop. Any ideas?

Dim cl As Range
Dim os As Integer

os = Range("C2").Value
For Each cl In Range("A:A")
If Not IsEmpty(cl) Then
ActiveCell.Offset(0, os).Resize(1, 13).Formula = _
"=SUMIF(Data!$A$7:$A$1000,$A11,Data!D$7:D$1000 )"
End If
Next cl

Dim cl As Range
Dim os As Integer

os = Range("C2").Value
For Each cl In Range("A:A")
If Not IsEmpty(cl) Then
ActiveCell.Offset(0, os).Resize(1, 13).Formula = _
"=SUMIF(Data!$A$7:$A$1000,$A$" & cl.row & ",Data!D$7:D$1000)"
End If
Next cl

--
Regards,
Tom Ogilvy


"Steph" wrote:

Hi. I have some code that loops through all cells in column A, and if a
cell is not blank, it inserts a Sumif formula. But I can't figure out how
to code the Criteria part of the formula to be Column A, Row whatever row
the loop is currently on. So in the code below, I need to replace $A11
within the formula with A & current row in the loop. Any ideas?

Dim cl As Range
Dim os As Integer

os = Range("C2").Value
For Each cl In Range("A:A")
If Not IsEmpty(cl) Then
ActiveCell.Offset(0, os).Resize(1, 13).Formula = _
"=SUMIF(Data!$A$7:$A$1000,$A11,Data!D$7:D$1000 )"
End If
Next cl