VLOOKUP, Mismatch due to _ space

[Sprucy]

Hi everyone,

My VLOOKUP formula is correct, but I get N/A# because of the value of D8 contains 3 letters and 1 space "YYZ ", but my range shows "YYZ" without the space...

My Range contains 9500 entries and should not change all that much.
As for my "D8" cell value, it will change daily and the info is obtained from an external source.

So, I do I get a correct match?

=VLOOKUP(D8,'9497 Airports'!$B$2:$K$10000,7,FALSE)

Cheers,
Sprucy

 

[Norie]

Sprucy

Is it D8 that has the space?

=VLOOKUP(TRIM(D8),'9497 Airports'!$B$2:$K$10000,7,0)

 

[Banker1]

You could also use something like this to trim all the strings within the data range. Run the macro first then create your formulas within Excel:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TrimRange()
<SPAN style="color:#00007F">Dim</SPAN> WorkRange <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("A1", ActiveCell.SpecialCells(xlCellTypeLastCell))

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> WorkRange
    <SPAN style="color:#00007F">If</SPAN> Cell.Value = "" <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">GoTo</SPAN> Next1
    <SPAN style="color:#00007F">ElseIf</SPAN> IsNumeric(Cell.Value) <SPAN style="color:#00007F">Then</SPAN>
        Cell.Value = Cell.Value * 1
    <SPAN style="color:#00007F">Else</SPAN>
        Cell.Value = Trim(Cell.Value)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Next1:
Next Cell

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Sprucy]

Hi Banker 1,

Your Macro work great at first, but I'm getting a error Bug 13 now.
I should of specified that Range is from D8 to D1000? (about 1000, different everyday...)



Sub TrimRange()
Dim WorkRange As Range

Set WorkRange = Range("d8", ActiveCell.SpecialCells(xlCellTypeLastCell))

For Each Cell In WorkRange
If Cell.Value = "" Then
GoTo Next1
ElseIf IsNumeric(Cell.Value) Then
Cell.Value = Cell.Value * 1
Else
Cell.Value = Trim(Cell.Value)
End If
Next1:
Next Cell

End Sub


Cheers,
Sprucy

 

[Banker1]

I believe Error 13 is a mismatch error. What is the value of the cell where the code breaks when you get the error?

As far as the data range, if the cells you need to trim always fall in Column D, specifically the first value in D8 I would use this:

<font face=Courier New><SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("D8:D" & Range("D65536").End(xlUp).Row)</FONT>

 

[Sprucy]

Hi Bannker1,

Sorry, never got back to say thanks.
Now my macro works perfectly and I'm happy to say that I've moved on to much more complex macro problems...

Cheers,
Sprucy