dynamic row declaration

[akkinarula]

i have following formula but in the following R10C2 is dynamic means R10 changes from R11 ,R12,and so on can i declared it as R where 'i' is taken from loop if there r any other way then plz sugest me

ActiveCell.FormulaR1C1 = _
"=COUNTIF('Customer''s Details'!R13C53:R262C53,(CONCATENATE(R10C2,R9C7)))"


thanks.

 

[DailyDoseofData]

so on can i declared it as R where 'i' is taken from loop

Hi,

Do you mean like this?

for i = 1 to 10
 
ActiveCell.FormulaR1C1 = _
"=COUNTIF('Customer''s Details'!R13C53:R262C53,(CONCATENATE(R" & i & "C2,R9C7)))"
 
'other stuff...
 
next i

Cheers =]

 

[akkinarula]

Hi,

Do you mean like this?

for i = 1 to 10
 
ActiveCell.FormulaR1C1 = _
"=COUNTIF('Customer''s Details'!R13C53:R262C53,(CONCATENATE(R" & i & "C2,R9C7)))"
 
'other stuff...
 
next i

Cheers =]

thanks it is running successfully.but result is not displaying

 

[Norie]

You need to do the concatenation in the code, not in the formula.

Where us this formula going to go?

Is it copied down a column?

If it is you might not need a loop, Excel should automatically change the row reference just as it does when you manually copy down a formula.

The only problem would be if the reference that needs to be changed was absolute.

I'm not too good with R1C1 so can't tell if that's the case.