Vlookup

[Faz]

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,'LII 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz

 

[Faz]

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

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())"

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,'LII 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz

 

[Gord Dibben]

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

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

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())"

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,'LII 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz

 

[Dave Peterson]

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.

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

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())"

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,'LII 200 raw
data'!$A2:$D$65000,4)),NA(),VLOOKUP($A2,'LII 200 raw
data'!$A2:$D$65000,4)),NA())

Thanks
Faz