VLOOKUP VALUE ERROR
in [Programming]
|
Prev: Printing sheets based on a cell value
Next: sumif help
From: Neecy on 25 May 2010 12:10 Hello- I am trying to conduct a lookup for text value from one table to another and receive an #VALUE! error. Here's the code: Range("A154").Formula = "=IF(ISNA(VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))=TRUE,,VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))" This works for my numeric lookups, but not the text. I even tried using the ISTEXT and ISNOTBLANK script and it doesn't work. Is there something I am overlooking. Thanks- Neecy
From: Dave Peterson on 25 May 2010 12:55 I'm not sure what you're doing, but I can't remember the time where I trying to match a range of cells in =vlookup() portion. =if(isna(vlookup(c114,'c:\..... Why are you using C114:I114? (In both spots in the formula, too) Neecy wrote: > > Hello- > I am trying to conduct a lookup for text value from one table to another and > receive an #VALUE! error. Here's the code: > > Range("A154").Formula = > "=IF(ISNA(VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))=TRUE,,VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))" > > This works for my numeric lookups, but not the text. I even tried using the > ISTEXT and ISNOTBLANK script and it doesn't work. Is there something I am > overlooking. > > Thanks- > > Neecy -- Dave Peterson
From: Neecy on 27 May 2010 16:29 Hi Dave- Were you able to resolve the error? "Neecy" wrote: > Hi Dave, thanks for answering back. The cells C114:I114 contain dates for 1 > week. The file I am looking in have the dates listed in vertical position of > the first column. All of the cells in the lookup table are numeric except > for the one I submitted to you. It's like the script is having a hard time > trying to obtain the values from this column. > > "Dave Peterson" wrote: > > > I'm not sure what you're doing, but I can't remember the time where I trying to > > match a range of cells in =vlookup() portion. > > > > =if(isna(vlookup(c114,'c:\..... > > > > Why are you using C114:I114? > > > > (In both spots in the formula, too) > > > > Neecy wrote: > > > > > > Hello- > > > I am trying to conduct a lookup for text value from one table to another and > > > receive an #VALUE! error. Here's the code: > > > > > > Range("A154").Formula = > > > "=IF(ISNA(VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))=TRUE,,VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))" > > > > > > This works for my numeric lookups, but not the text. I even tried using the > > > ISTEXT and ISNOTBLANK script and it doesn't work. Is there something I am > > > overlooking. > > > > > > Thanks- > > > > > > Neecy > > > > -- > > > > Dave Peterson > > . > >
From: Dave Peterson on 27 May 2010 16:39 I still don't understand the formula you're using. Did you try my suggestion? Neecy wrote: > > Hi Dave- > Were you able to resolve the error? > > "Neecy" wrote: > > > Hi Dave, thanks for answering back. The cells C114:I114 contain dates for 1 > > week. The file I am looking in have the dates listed in vertical position of > > the first column. All of the cells in the lookup table are numeric except > > for the one I submitted to you. It's like the script is having a hard time > > trying to obtain the values from this column. > > > > "Dave Peterson" wrote: > > > > > I'm not sure what you're doing, but I can't remember the time where I trying to > > > match a range of cells in =vlookup() portion. > > > > > > =if(isna(vlookup(c114,'c:\..... > > > > > > Why are you using C114:I114? > > > > > > (In both spots in the formula, too) > > > > > > Neecy wrote: > > > > > > > > Hello- > > > > I am trying to conduct a lookup for text value from one table to another and > > > > receive an #VALUE! error. Here's the code: > > > > > > > > Range("A154").Formula = > > > > "=IF(ISNA(VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))=TRUE,,VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))" > > > > > > > > This works for my numeric lookups, but not the text. I even tried using the > > > > ISTEXT and ISNOTBLANK script and it doesn't work. Is there something I am > > > > overlooking. > > > > > > > > Thanks- > > > > > > > > Neecy > > > > > > -- > > > > > > Dave Peterson > > > . > > > -- Dave Peterson
From: Neecy on 27 May 2010 17:51
Hi Dave, thanks for answering back. The cells C114:I114 contain dates for 1 week. The file I am looking in have the dates listed in vertical position of the first column. All of the cells in the lookup table are numeric except for the one I submitted to you. It's like the script is having a hard time trying to obtain the values from this column. "Dave Peterson" wrote: > I'm not sure what you're doing, but I can't remember the time where I trying to > match a range of cells in =vlookup() portion. > > =if(isna(vlookup(c114,'c:\..... > > Why are you using C114:I114? > > (In both spots in the formula, too) > > Neecy wrote: > > > > Hello- > > I am trying to conduct a lookup for text value from one table to another and > > receive an #VALUE! error. Here's the code: > > > > Range("A154").Formula = > > "=IF(ISNA(VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))=TRUE,,VLOOKUP(C114:I114,'C:\test\DIC\site\[billing.xls]billing'!$A$1:$AI$32,34,FALSE))" > > > > This works for my numeric lookups, but not the text. I even tried using the > > ISTEXT and ISNOTBLANK script and it doesn't work. Is there something I am > > overlooking. > > > > Thanks- > > > > Neecy > > -- > > Dave Peterson > . > |