Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |