Sumif within loop

[Steph]

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

[Tom Ogilvy]

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
>
>
>