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