Vlookup
 

I have the following statement on an excel spreadsheet. Please help me to
convert this to either a macro or vba code, the latter preferably.

=IF($A2-(VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,1))<1,IF(ISERROR(VLOOKUP($A2,'L II 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz

Vlookup
 

Range("F3").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C1-(VLOOKUP(R[-1]C1,'LII 200 raw " & Chr(10) &
"data'!R[-1]C1:R65000C4,1))<1,IF(ISERROR(VLOOKUP(R[-1]C1,'LII 200 raw " &
Chr(10) & "data'!R[-1]C1:R65000C4,4)),NA(),VLOOKUP(R[-1]C1,'LII 200 raw " &
Chr(10) & "data'!R[-1]C1:R65000C4,4)),NA())"


"Faz" wrote:

I have the following statement on an excel spreadsheet. Please help me to
convert this to either a macro or vba code, the latter preferably.

=IF($A2-(VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,1))<1,IF(ISERROR(VLOOKUP($A2,'L II 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz

Vlookup
 

Thank you for your help. What is chr(10)?
Faz

"dmoney" wrote:

Range("F3").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C1-(VLOOKUP(R[-1]C1,'LII 200 raw " & Chr(10) &
"data'!R[-1]C1:R65000C4,1))<1,IF(ISERROR(VLOOKUP(R[-1]C1,'LII 200 raw " &
Chr(10) & "data'!R[-1]C1:R65000C4,4)),NA(),VLOOKUP(R[-1]C1,'LII 200 raw " &
Chr(10) & "data'!R[-1]C1:R65000C4,4)),NA())"


"Faz" wrote:

I have the following statement on an excel spreadsheet. Please help me to
convert this to either a macro or vba code, the latter preferably.

=IF($A2-(VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,1))<1,IF(ISERROR(VLOOKUP($A2,'L II 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz

Vlookup
 

Chr(10) is an in-cell linefeed. Not sure why it was used in this case but
no harm, no foul

BTW...............a macro is written in VBA code so it is not a case of
either/or


Gord Dibben MS Excel MVP

On Sun, 10 Aug 2008 07:14:01 -0700, Faz
wrote:

Thank you for your help. What is chr(10)?
Faz

"dmoney" wrote:

Range("F3").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C1-(VLOOKUP(R[-1]C1,'LII 200 raw " & Chr(10) &
"data'!R[-1]C1:R65000C4,1))<1,IF(ISERROR(VLOOKUP(R[-1]C1,'LII 200 raw " &
Chr(10) & "data'!R[-1]C1:R65000C4,4)),NA(),VLOOKUP(R[-1]C1,'LII 200 raw " &
Chr(10) & "data'!R[-1]C1:R65000C4,4)),NA())"


"Faz" wrote:

I have the following statement on an excel spreadsheet. Please help me to
convert this to either a macro or vba code, the latter preferably.

=IF($A2-(VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,1))<1,IF(ISERROR(VLOOKUP($A2,'L II 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz

Vlookup
 

Just to add to what Gord wrote...

This character is used in the name of the worksheet. This seems pretty odd to
me.

If the name of the sheet is really "LII 200 raw " & chr(10) & "data", then the
user will see a square box in the worksheet tab. It really won't force a
linefeed in the tab.



Faz wrote:

Thank you for your help. What is chr(10)?
Faz

"dmoney" wrote:

Range("F3").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C1-(VLOOKUP(R[-1]C1,'LII 200 raw " & Chr(10) &
"data'!R[-1]C1:R65000C4,1))<1,IF(ISERROR(VLOOKUP(R[-1]C1,'LII 200 raw " &
Chr(10) & "data'!R[-1]C1:R65000C4,4)),NA(),VLOOKUP(R[-1]C1,'LII 200 raw " &
Chr(10) & "data'!R[-1]C1:R65000C4,4)),NA())"


"Faz" wrote:

I have the following statement on an excel spreadsheet. Please help me to
convert this to either a macro or vba code, the latter preferably.

=IF($A2-(VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,1))<1,IF(ISERROR(VLOOKUP($A2,'L II 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz

--

Dave Peterson