VBA formula error

[zabiullakhan]

I get an error where i have marked red in the formula..

ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[" & SIS & "],RG,2,0)),[COLOR=red]""[/COLOR],VLOOKUP(RC[" & SIS & "],RG,2,0))"

Please help

 

[ukmikeb]

Hi

Try -

ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[" & SIS & "],RG,2,0[COLOR=red][COLOR=black])),[/COLOR]""""[/COLOR][COLOR=black],[/COLOR]VLOOKUP(RC[" & SIS & "],RG,2,0))"

Use double quotes when you have quotes in a string.

hth

 

[analystooma11]

Hi, Is there anyone who could let me know why I'm getting an #N/A result on this formula? I need to get the values of the npanxx from C column. It doesn't show the column here but its the 3 from the left. thanks.

=VLOOKUP(C2,$F$2:$G$8,2,0)

npa nxx npanxx rtv npanxx values
201 203 201203 #N/A 201203 0.007705
201 204 201204 #N/A 201266 0.00943
201 210 201210 #N/A 201286 0.001467
201 247 201247 #N/A 201247 0.00138
201 266 201266 #N/A 201204 0.008855
201 286 201286 #N/A 201291 0.005175
201 291 201291 #N/A 201210 0.011615

 

[Michael M]

First up welcome to the Board.
But in future please start a new thread rather than jumping on the end of a similar one.
In answer to your question
If npanxx is the 3rd column, your formula should read

=VLOOKUP(C2,$F$2:$G$8,3,0)

The other possibility for the #N/A, is that C2 has a value that doesn't match in the VLOOKUP table.

 

[ukmikeb]

Hi

Your formula is pointing to the wrong columns!

Try -

=VLOOKUP(C2,$E$2:$F$8,2,0)

@MichaelM - Your formula creates a #REF error, you can't refer to a column outside the specified range!

hth

 

[Michael M]

Yeah, point Mike.
I was only looking at the required column in the VLOOKUP.
Didn't look at the range .....doh !!

 

[analystooma11]

Yeah, point Mike.
I was only looking at the required column in the VLOOKUP.
Didn't look at the range .....doh !!

Thank you. I did try changing the 2nd to 3rd column reference but didn't help. I found out that there were (hidden) green tabs on the cell which creates an error.