match, vlookup, etc. works sporadically due to apparent formatting
differences. i am trying to match the contents of 1 cell with it's
"look-a-like" in an array. the problem comes b/c many of the inputs come
from a database or some other form and may be formatted as something other
than my 1 cell, like as text, with spaces, etc. i have tried trim, .value,
etc. "if it looks like it matches w/ the naked eye, it's a match" is what i
am looking for.

Sub matchingdata()
'Dim loanorig As ???? 'tried a few things here
loanorig = Range("o30") 'tried .value and trim(...)
loansold = Range("n30")
loanorigrow = Application.Match(loanorig, Range("a1:a500"), 0)
'PROBLEM
loansoldrow = Application.Match(loansold,Range"a1:a500"),0)
'more code here
End Sub

these are in range("n30") and range("o30"), respectively.
16004665102

6004665102


in range("a1:a500") both of these are certainly there by the looks of it,
but only "n30" works, while "o30" causes: the variable "loanorigrow" to
return "type mismatch" with "error 2042" as that variable.
the bottom line is i need a sure-fire, consistent way of matching apparently
identical data, no matter what form it is in, which will be many. "if it
looks like it matches w/ the naked eye, it's a match" is the option i need
in this case. any ideas? thanks, mike allen

Hi Mike,

You may find a visit to David McRitchie's TrimAll page with its discussion
notes and TrimAll routines useful:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


---
Regards,
Norman


" wrote in message
...
match, vlookup, etc. works sporadically due to apparent formatting
differences. i am trying to match the contents of 1 cell with it's
"look-a-like" in an array. the problem comes b/c many of the inputs come
from a database or some other form and may be formatted as something other
than my 1 cell, like as text, with spaces, etc. i have tried trim,
..value,
etc. "if it looks like it matches w/ the naked eye, it's a match" is what
i
am looking for.

Sub matchingdata()
'Dim loanorig As ???? 'tried a few things here
loanorig = Range("o30") 'tried .value and trim(...)
loansold = Range("n30")
loanorigrow = Application.Match(loanorig, Range("a1:a500"), 0)
'PROBLEM
loansoldrow = Application.Match(loansold,Range"a1:a500"),0)
'more code here
End Sub

these are in range("n30") and range("o30"), respectively.
16004665102

6004665102


in range("a1:a500") both of these are certainly there by the looks of it,
but only "n30" works, while "o30" causes: the variable "loanorigrow" to
return "type mismatch" with "error 2042" as that variable.
the bottom line is i need a sure-fire, consistent way of matching
apparently
identical data, no matter what form it is in, which will be many. "if it
looks like it matches w/ the naked eye, it's a match" is the option i
need
in this case. any ideas? thanks, mike allen