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


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=467694

Correct VBA syntax for cell function formula
 

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" wrote in
message ...

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

Correct VBA syntax for cell function formula
 

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" wrote in
message ...

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