vlookup help please

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Board Regular
Joined
Feb 17, 2004
Messages
82
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Is the range A1:D57 sorted in ascending order?

Does the macro program fail?

Are you getting unexpected answers?
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
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

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

Board Regular
Joined
Feb 17, 2004
Messages
82
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,653
Messages
5,597,377
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top