Macro Help with Variable

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

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))"
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Expected expression error help 2
formula variable prob. 2
Variable in formula problem 1
Row Count... 4
Excel VBA 1
Checking cells for input 1
Counter stops working 4
Automatically selecting active chart 4

Back
Top