match problems
 

i posted this last week and have since found a problem with it. this is a
very small sample of the main array:

123
abc
#345
67#
'89

some are text, some are numbers; the problem is the one that looks like a
number (isnumeric), but is actually text: '89, the last one.

if i have a seperate list of (important that in this case, they all have '
in front (text):

'67#
'123
'89

i want to know what row in the array (which starts on row1) each of these
are in. the answers should be: 4, 1, 5.

this is the code that gets: 4, 1, "no match made", which is errroneous.

Dim loanorig As Variant
Dim loanorigrow As Variant
Dim rng As Range
Set rng = Range("a1:a5")

For i = 1 To 3
loanorig = Range("c" & i).Value

If IsNumeric(loanorig) Then
loanorigrow = Application.Match(CDbl(loanorig), rng, 0)
Else
loanorigrow = Application.Match(loanorig, rng, 0)
End If

If IsError(loanorigrow) Then
MsgBox "No match made"
Else
Range("e" & i) = loanorigrow
End If

Next i

the old problem was if the apparent match in the array truely was a number,
i had to change my field to be compared to a number if it was numeric (could
be converted to number). this is what tom ogilvy came up w/ last time, and
it worked. the new problem arises when the apparent match in the array
looks like a number, but is text. any thoughts? thanks, mike allen

match problems
 

If the number fails, then try it again as text.

Dim loanorig As Variant
Dim loanorigrow As Variant
Dim rng As Range
Set rng = Range("a1:a5")

For i = 1 To 3
loanorig = Range("c" & i).Value

If IsNumeric(loanorig) Then
loanorigrow = Application.Match(CDbl(loanorig), rng, 0)
if iserror(loanorigrow) then
loanorigrow = Application.Match(loanorig,rng,0)
End if
Else
loanorigrow = Application.Match(loanorig, rng, 0)
End If

If IsError(loanorigrow) Then
MsgBox "No match made"
Else
Range("e" & i) = loanorigrow
End If

Next i

if you have a text 89 and a number 89, the number 89 will win.

--
Regards,
Tom Ogilvy





"mike allen" wrote in message
...
i posted this last week and have since found a problem with it. this is a
very small sample of the main array:

123
abc
#345
67#
'89

some are text, some are numbers; the problem is the one that looks like a
number (isnumeric), but is actually text: '89, the last one.

if i have a seperate list of (important that in this case, they all have '
in front (text):

'67#
'123
'89

i want to know what row in the array (which starts on row1) each of these
are in. the answers should be: 4, 1, 5.

this is the code that gets: 4, 1, "no match made", which is errroneous.

Dim loanorig As Variant
Dim loanorigrow As Variant
Dim rng As Range
Set rng = Range("a1:a5")

For i = 1 To 3
loanorig = Range("c" & i).Value

If IsNumeric(loanorig) Then
loanorigrow = Application.Match(CDbl(loanorig), rng, 0)
Else
loanorigrow = Application.Match(loanorig, rng, 0)
End If

If IsError(loanorigrow) Then
MsgBox "No match made"
Else
Range("e" & i) = loanorigrow
End If

Next i

the old problem was if the apparent match in the array truely was a
number,
i had to change my field to be compared to a number if it was numeric
(could
be converted to number). this is what tom ogilvy came up w/ last time,
and
it worked. the new problem arises when the apparent match in the array
looks like a number, but is text. any thoughts? thanks, mike allen