Correct VBA syntax for cell function formula

The following formula works great in a worksheet cell at counting every 5th row (5 rows total) that contains data:

=SUMPRODUCT(--(A1:A100<>""),--(MOD(ROW(A1:A100),5)=1))

However, I can't get the correct syntax for it to work in VBA code.
Can someone help? Thanks a million. mikeburg

The only way that I know to use formulas like that in VBA is using the
Evaluate() method. What part of that are you trying to use from VBA ?

--
Regards,

Juan Pablo González
Excel MVP

"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> The following formula works great in a worksheet cell at counting every
> 5th row (5 rows total) that contains data:
>
> =SUMPRODUCT(--(A1:A100<>""),--(MOD(ROW(A1:A100),5)=1))
>
> However, I can't get the correct syntax for it to work in VBA code.
> Can someone help? Thanks a million. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
> http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=467694
>

mikeburg,

This will sum every 5th row in a column. It

Sub Sum_Column()
Dim i As Integer
Dim x As Integer
Dim rng As Range

For i = 1 To 20 ' 5*20 = 100 cells

x = (i - 1) * 5

With Worksheets("Data") 'worksheet name
Set rng = .Range(.Cells(1 + x, 1), .Cells(5 + x, 1)) ' how to define range
to sum
..Cells(5 * i, 3) = Application.WorksheetFunction.Sum(rng) 'sum the range and
place it in cell (c5, c10, etc)
End With

Next i

End Sub


"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> The following formula works great in a worksheet cell at counting every
> 5th row (5 rows total) that contains data:
>
> =SUMPRODUCT(--(A1:A100<>""),--(MOD(ROW(A1:A100),5)=1))
>
> However, I can't get the correct syntax for it to work in VBA code.
> Can someone help? Thanks a million. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
> http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=467694
>