vlookup help please

[sasoderl]

Hi, I have written a macro that performs a vlookup. Originally, the column of data that vlookup uses was soley numerical, however, now, I need it to be a text column (with some numbers and some text). From my reading, it seems that vlookup should still work, however, it is not working. Does anyone have any suggestions.

 

[just_jon]

Sounds like you're using an approximate match on unsorted data, or looking to match numeric-to-text. Can you post the code in question?

 

[sasoderl]

Here is the code:

Sub SubmissionFeeCategoryMacro()

Range("B1").Select
ActiveCell.Formula = "=VLOOKUP(A1, [DATA.xls]Sheet1!$A$1:$D$57, 2, FALSE)"

Range("C1").Select
ActiveCell.Formula = "=VLOOKUP(A1, [DATA.xls]Sheet1!$A$1:$D$57, 3, FALSE)"

Range("D1").Select
ActiveCell.Formula = "=VLOOKUP(A1, [DATA.xls]Sheet1!$A$1:$D$57, 4, FALSE)"

Range("B1:D1").AutoFill Destination:=Range("B1:D" & _
InputBox("Please Enter the Number of Rows to Fill Down", _
"Autofill Range")), Type:=xlFillDefault

End Sub

 

[onlyadrafter]

Hello,

Is the range A1:D57 sorted in ascending order?

Does the macro program fail?

Are you getting unexpected answers?

 

[just_jon]

What's the A1 value that fails? What is its underlying format? What is the underlying format of the presumptive match?

 

[sasoderl]

All values fail. Okay, more details:

My column A in DATA.xls is all numeric, but I have formatted the cells to be text. The worksheet that I am trying to run the macro on has the data defined as text. I am basically taking information from a project (.mpp) file in Microsoft Project and mapping some of the information into an excel worksheet. The information I am mapping from Microsoft Project is the data that is defined as text (in Microsoft Project). If I define that particular data as number in Microsoft Project and then create the map, my macro works. The problem is, not all the data in that field will actually be numeric. It has to be a text field. Does this help explain my problem?

 

[just_jon]

As long as the formats are equal all should be well; however, as you are importing the data from another app, it may be dirty.

If you do =LEN(ref) to the cells, do you get a number which is equal to the visual length you can see in the cell?

 

[sasoderl]

I don't really understand what you mean by "If you do =LEN(ref) to the cells, do you get a number which is equal to the visual length you can see in the cell?". (I am rather new at this)

 

[just_jon]

OK; Say you think cell A1 should find a match in cell A22 of your DATA lookup; in an unused cell of DATA, enter =LEN(A22) and then see if the number returned is equal to the number of characters you can see in the cell.

We're basically trying to find out if there are non-visible characters in A22 by havintg Excel tell us the lebgth of its contents. Do the same w/ A1.

 

[sasoderl]

Ok, I did that, and I am getting the correct length.