Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup text in vba
Hi to all...
I am currently using Excel 2003. I am trying to use vlookup in VBA. First I have a workbook with 8 spreadsheets. I have a Master Parts List spreadsheet that updates 7 other worksheets within the workbook with the Vlookup in each cell looking back at the Master Parts List spreadsheet. I would like to accomplish this in VBA when I go to any one of the other spread sheets in column A active cell value I type it looks up part number, description, and unit of measure. I need the value in column A to remain text because I have both numbers and alpha characters as part numbers. Here is what I have and I keep getting #N/A. Sub Lookup() Dim lookuprng As Range Dim myVal As String 'or whatever Set lookuprng = Worksheets("Master Parts List").Range("$A$8:$D$5000") myVal = ActiveCell.Value ActiveCell.Offset(0, 1) = Application.VLookup(myVal, lookuprng, 2, False) If myVal = "" Then ActiveCell.Offset(0, 1) = "" End If End Sub Thanks for any help I can get. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup text in vba
Try
Dim myVal As Variant If this post helps click Yes --------------- Jacob Skaria "John" wrote: Hi to all... I am currently using Excel 2003. I am trying to use vlookup in VBA. First I have a workbook with 8 spreadsheets. I have a Master Parts List spreadsheet that updates 7 other worksheets within the workbook with the Vlookup in each cell looking back at the Master Parts List spreadsheet. I would like to accomplish this in VBA when I go to any one of the other spread sheets in column A active cell value I type it looks up part number, description, and unit of measure. I need the value in column A to remain text because I have both numbers and alpha characters as part numbers. Here is what I have and I keep getting #N/A. Sub Lookup() Dim lookuprng As Range Dim myVal As String 'or whatever Set lookuprng = Worksheets("Master Parts List").Range("$A$8:$D$5000") myVal = ActiveCell.Value ActiveCell.Offset(0, 1) = Application.VLookup(myVal, lookuprng, 2, False) If myVal = "" Then ActiveCell.Offset(0, 1) = "" End If End Sub Thanks for any help I can get. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup text in vba
Is the first column of the table also text? (Not just formatted as text, but
really text???) Just changing the format from number to text (or text to number) isn't enough to change the underlying values. You can either fix the data so that it's consistent (all text or all numbers) or you can use =vlookup() twice. I used clng() to change text numbers to a long integer. If your part numbers are decimals, you'll want to use cdbl() instead of clng(). Personally, this kind of lookup scares me. I'd invest the time and fix the data so that it's consistent. Option Explicit Sub myLookup() Dim lookuprng As Range Dim myVal As Variant Dim res As Variant Set lookuprng = Worksheets("Master Parts List").Range("$A$8:$D$5000") myVal = ActiveCell.Value If myVal = "" Then res = "" Else 'look for a match as a string res = Application.VLookup(myVal & "", lookuprng, 2, False) If IsError(res) Then 'look for a match as a number If IsNumeric(myVal) Then res = Application.VLookup(CLng(myVal), lookuprng, 2, False) If IsError(res) Then 'it wasn't found twice! res = "Not found" ' "" when you're done testing End If End If End If End If ActiveCell.Offset(0, 1) = res End Sub I wouldn't use a name that is also a function name in excel. John wrote: Hi to all... I am currently using Excel 2003. I am trying to use vlookup in VBA. First I have a workbook with 8 spreadsheets. I have a Master Parts List spreadsheet that updates 7 other worksheets within the workbook with the Vlookup in each cell looking back at the Master Parts List spreadsheet. I would like to accomplish this in VBA when I go to any one of the other spread sheets in column A active cell value I type it looks up part number, description, and unit of measure. I need the value in column A to remain text because I have both numbers and alpha characters as part numbers. Here is what I have and I keep getting #N/A. Sub Lookup() Dim lookuprng As Range Dim myVal As String 'or whatever Set lookuprng = Worksheets("Master Parts List").Range("$A$8:$D$5000") myVal = ActiveCell.Value ActiveCell.Offset(0, 1) = Application.VLookup(myVal, lookuprng, 2, False) If myVal = "" Then ActiveCell.Offset(0, 1) = "" End If End Sub Thanks for any help I can get. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
look for text not using vlookup and sum if | Excel Discussion (Misc queries) | |||
VLOOKUP (with text) | Excel Discussion (Misc queries) | |||
Using VLOOKUP and text | Excel Discussion (Misc queries) | |||
Vlookup using text | Excel Worksheet Functions | |||
Vlookup a value and text | Excel Discussion (Misc queries) |