Variable Referencing problem

[jbesant]

Hi everyone:

I am using the vba code below to input a formula into excel cells. However, I want to make the "A:A" and the "D3" in the formula relative to the variable c2 (which is an integer). Does anyone know how to do this?

Cells(3, 3).Offset(0, c2) = "=IF(COUNTIF(" & "A:A" & "," & "D3" & " )=0,""Not Found"",""OK"")"

Thanks so much for your help!

Jamie

 

[canadapip]

Try:

Cells(3, 3).Offset(0, c2) = "=IF(COUNTIF(" & "A:A" & "," & "D" & c2 & " )=0,""Not Found"",""OK"")"

assuming c2 is an integer equal to 10, the formula should insert into the cell as
=IF(COUNTIF(A:A,D10)=0,"Not Found","OK")

if its equal to 34, the formula should insert into the cell as
=IF(COUNTIF(A:A,D34)=0,"Not Found","OK")

You may want to add some form of error checking to make sure that the value of C2 is a valid interger of at least 1.

 

[VoG]

A:A wouldn't change (it is a whole column reference) but what would you expect D3 to be depending on c2?

 

[jbesant]

Canadapip - thanks for noticing that "D3" could be changed so simply. I will create another variable (x=c2+3), and concantate from there.

I still need to change the column reference according to c2. Reason for this is that I'm using a loop and each time it runs, I need to column reference to change by c2 to find the appropriate column.

 

[jbesant]

Sorry Peter - just read your reply... thanks for the interest in my problem. Do you have any ideas how to change the column reference?

 

[VoG]

How do you want the column reference to change depending on c2?

 

[jbesant]

If column A = 1, then I want the column reference to equal 1+c2...

 

[VoG]

If c2 cannot exceed 25 then perhaps

Cells(3, 3).Offset(0, c2) = "=IF(COUNTIF(" & Chr(34) & Chr(1 + c2) & Chr(34) & "," & "D" & c2 & " )=0,""Not Found"",""OK"")"

 

[jbesant]

hmm...vba can't compile. Think its a problem with the CHR function. Any ideas?

 

[VoG]

I haven't tested the code but it compiles OK.

Any indication of what is wrong?