vlookup text in vba

[John]

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.

 

[Jacob Skaria]

Try

Dim myVal As Variant

If this post helps click Yes

 

[Don Guillett]

I tested yours and it worked fine. You may have leading or trailing spaces
that need trimming.

Sub Lookups()
Dim lookuprng As Range
Dim myVal As String 'or whatever
Set lookuprng = Worksheets("Master Parts List").Range("A85000")
With ActiveCell
If .Value <> "" Then
.Offset(, 1) = Application.VLookup(Trim(.Value), lookuprng, 3, 0)
End If
End With
End Sub

 

[Dave Peterson]

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.