Rici
 

Hi,
If this formula
=(VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3),FALSE))
is converting as
"=(VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"
then what will be the following
=IF(ISNA(VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3), FALSE)),"",VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3 ),FALSE))
I wanted to instert the line of code into a macro.
thanks and regards
nowfa

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=40129

Rici
 

Try:

Range("...").FormulaR1C1 =
"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"

Regards

Trevor


"nowfal" wrote in
message ...

Hi,
If this formula
=(VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3),FALSE))
is converting as
"=(VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"
then what will be the following
=IF(ISNA(VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3), FALSE)),"",VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3 ),FALSE))
I wanted to instert the line of code into a macro.
thanks and regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=401299

Rici
 

Hi Trevor,
It is working, thank you very much.
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=401299

Rici
 

You're very welcome; thanks for letting me know.

Regards


"nowfal" wrote in
message ...

Hi Trevor,
It is working, thank you very much.
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=401299

Rici
 

Hi Trevor,
If I want to do the same in multiple cells what will b
the code.
Say it is now in L2 only, suppose I need in L2, R2, W2. Pls let me kno
the way.
thanks and regards
nowfa

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=40129

Rici
 

Couple of ways:

The first is just to put the same formula in each cell. The second is to
put it in L2 and then copy it to the others.

Sub Test()
Range("L2").FormulaR1C1 = _
"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"
Range("R2").FormulaR1C1 = _
"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"
Range("W2").FormulaR1C1 = _
"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"
End Sub

Sub Test2()
Range("L2").FormulaR1C1 = _
"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"
Range("L2").Copy Range("R2")
Range("L2").Copy Range("W2")
End Sub

Regards

Trevor


"nowfal" wrote in
message ...

Hi Trevor,
If I want to do the same in multiple cells what will be
the code.
Say it is now in L2 only, suppose I need in L2, R2, W2. Pls let me know
the way.
thanks and regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=401299

Rici
 

Hi Trevor,
I have got a better way by adding the cells in the same
line as
Range("L2,R2,W2").FormulaR1C1 =
"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"
thanks for your reply.
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=401299

Rici
 

OK. Three ways. ;-) No doubt there are others.

Glad you have a solution to your question


"nowfal" wrote in
message ...

Hi Trevor,
I have got a better way by adding the cells in the same
line as
Range("L2,R2,W2").FormulaR1C1 =
"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"
thanks for your reply.
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=401299