Excel 2003 & VBA: VLookup question

[davis07]

Hi, I am trying to get the following equation to work. I want the vlookup command to search columns A thru C, but the formula doesn't work as is. I've tried relative references too, but can't get it to work.

ActiveCell.FormulaR1C1 = _
"=IF(RC[5]=""NO"",NOW(),VLOOKUP(TRIM(RC[-15]),OtherSheet!" & "A:C" & ",3,FALSE))"

The above formula yields:
=IF(V2="NO",NOW(),VLOOKUP(TRIM(B2),OtherSheet!A:Q:Q,3,FALSE))

Thanks in advance!

 

[ck1999]

How are you getting A:Q:Q? Also, what do you mean by does not work? What error are you getting?

Is your lookup table actually on sheet called othersheet and is the value of b2 in column a of othersheet?


ck1999

 

[davis07]

When I run the VBA program, it runs that line in the code and inserts what I have indicated as 'yields'... I don't know where it is getting the Q:Q. Yes, the look-up value IS in the othersheet (name changed for simplicity). I want it to insert:

=IF(V2="NO",NOW(),VLOOKUP(TRIM(B2),OtherSheet!A:C,3,FALSE))

Any tips?
Thanks in advance.

 

[ck1999]

try

"=IF(RC[5]=""NO"",NOW(),VLOOKUP(TRIM(RC[-15]),OtherSheet!A:C,3,FALSE))"


ck1999

 

[PHV]

OtherSheet!A:C is not a valid R1C1 notation.
You may try this:

ActiveCell.Formula = _
   "=IF(V2=""NO"",NOW(),VLOOKUP(TRIM(B2),OtherSheet!A:C,3,FALSE))"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

You may also try this:

ActiveCell.FormulaR1C1 = _
   "=IF(RC[5]=""NO"",NOW(),VLOOKUP(TRIM(RC[-15]),othersheet!C1:C3,3,FALSE))"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davis07]

Thank you PHV and ck1999. The second formula suggestion worked great. Mixing the R1C1 and regular cell references wasn't working. Thanks again!