I calculate my variable counter by counting the number of rows in the
previous worksheet. The value ends up being 107, which is correct.

The problem I have is that I have to later functions I want to use that have
a varying number of rows and I want use the counter variable as the row
number. Basically, I want the statement below to work, except replacing the
107 with counter.

'First Shift Failure Data
x = 2
Do While Cells(x, 1).Value < ""
Cells(x, 5).Value =
"=SUMPRODUCT(--(Data!R3C[4]:R[107]C[4]=""FIRST""),--(Data!R3C[5]:R[107]C[5]=RC1))"
x = x + 1
Loop

Any help is appreciated

assuming "counter" is the name of your variable, try changing your formula to
this
"=SUMPRODUCT(--(Data!R3C[4]:R[" & counter &
"]C[4]=""FIRST""),--(Data!R3C[5]:R[" & counter & "]C[5]=RC1))"

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Ben" wrote:

I calculate my variable counter by counting the number of rows in the
previous worksheet. The value ends up being 107, which is correct.

The problem I have is that I have to later functions I want to use that have
a varying number of rows and I want use the counter variable as the row
number. Basically, I want the statement below to work, except replacing the
107 with counter.

'First Shift Failure Data
x = 2
Do While Cells(x, 1).Value < ""
Cells(x, 5).Value =
"=SUMPRODUCT(--(Data!R3C[4]:R[107]C[4]=""FIRST""),--(Data!R3C[5]:R[107]C[5]=RC1))"
x = x + 1
Loop

Any help is appreciated