VBA - IsNumber Returns Nothing Due to Prefix

Follow Up Question from:

https://stackoverflow.com/questions/50227693/vba-match-lookup-with-multiple-parameters

The code below works well, but I have since realized that the values in column A sometimes contain a prefix, which therefore returns nothing.

Hoping someone can edit the actually formula to search for exact string matches perhaps instead of number matches?

Code:

Sub PlaceFormula()
Dim ws As Worksheet
Dim lr As Long
Dim lc As Long

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With

Set ws = Sheets("Main")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column

ws.Range("B2", ws.Cells(lr, lc)).Formula = "=IF(ISNUMBER(MATCH($A2&B$1,INDEX(tStatus[[Employee Number]:[Employee Number]]&tStatus[[Wk Number]:[Wk Number]],),0)),""Match"","""")"
ws.Range("B2", ws.Cells(lr, lc)).Value = ws.Range("B2", ws.Cells(lr, lc)).Value

With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

Open in new window


Sample of data in Column A:

12345

24681

78956

AB12345

A78956

The data in the table will also contain prefix IF the value is there.. So essentially, the MATCH is looking for an exact match.

Sample file attached.

I posted here as well:

https://stackoverflow.com/questions/50488732/vba-isnumber-returns-nothing-due-to-prefix

And the only reply I have received since, is to identify the number of chars in the string, then use that number to split the string and then I am left with only the number without the prefix. The problem is that I believe this might bring up some false positives - The data in the table also contains prefix'd numbers and non-prefix'd numbers, so it is very possible that after the prefix is removed, it might end up matching with the same number in the table but that does not necessarily meant that it is the correct number.

Apart from this, I recon I would need to apply the same method to my table so as to attempt tp match 12356 with 123456 instead of trying to match 123456 with AB123456.
tt-Match_Lookup_and_Copy_Column.xlsm
Eitel DagninIT Security AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Is the prefix always AB? If it is then use a Custom Number Format

See the Custom Format for A3
tt-Match_Lookup_and_Copy_Column.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try...

Sub PlaceFormula()
Dim ws As Worksheet
Dim lr As Long
Dim lc As Long

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableAnimations = False
End With

Set ws = Sheets("Main")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column

ws.Range("B2", ws.Cells(lr, lc)).Formula = "=IF(ISNUMBER(MATCH(GetNumber($A2)&B$1,INDEX(tStatus[[Employee Number]:[Employee Number]]&tStatus[[Wk Number]:[Wk Number]],),0)),""Match"","""")"
ws.Range("B2", ws.Cells(lr, lc)).Value = ws.Range("B2", ws.Cells(lr, lc)).Value

With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub


Function GetNumber(rng As Range)
Dim Num As Long
Dim RE As Object, Match As Object, Matches As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
   .Global = True
   .Pattern = "\d"
End With
If RE.test(rng.Value) Then
   Set Matches = RE.Execute(rng.Value)
   For Each Match In Matches
      Num = Num & Match.Value
   Next Match
End If
GetNumber = Num
End Function

Open in new window

tt-Match_Lookup_and_Copy_Column.xlsm
0
aikimarkCommented:
apply this function to the columnA values in the Main worksheet
Public Function GetDigits(ByVal parmCellText) As String
    Static oRE As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = False
        oRE.Pattern = "\d+"
    End If
    If oRE.test(parmCellText) Then
        GetDigits = oRE.Execute(parmCellText)(0).Value
    Else
        GetDigits = vbNullString
    End If
End Function

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Eitel DagninIT Security AdministratorAuthor Commented:
Hi All,

Thank you very much for all your replies, it is much appreciated..

@Roy, your solution works great! :) But I seemed to have made a mistake when I uploaded my sample workbook.. In the table on the second sheet, the values in the Employee Number field are only numbers and do not have any text in front of them.. This is a mistake as the data in the table is supposed to include Employee Numbers that have text in front of them. And to answer your question, no the prefix might be "CD" or perhaps just "J".. But this seems to be working just fine :)

@Subodh Tiwari (Neeraj) & @aikimark, I used both your functions in my code and applied it the way you did, but it still does not find any matches. I ran the code with today's data and there is definitely one match. If I copy the Employee Number from the Main sheet to the sheet with the table and I do a vlookup, I find a match for a few of the app's. If you read my reply to Roy above, would my response affect the code sample you have supplied?
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
aikimarkCommented:
Are you applying my function to the value that you are looking up?  Your sample workbook didn't have any letter prefix data, so this function is only for one parameter of the match function.

What does your formula look like?
0
Eitel DagninIT Security AdministratorAuthor Commented:
Hi @aikimark

I am using Roy's answer which is:

ws.Range("B2", ws.Cells(lr, lc)).Formula = "=IF(ISNUMBER(MATCH($A2&B$1,INDEX(tStatus[[Employee Number]:[Employee Number]]&tStatus[[Wk Number]:[Wk Number]],),0)),""Match"","""")"
    ws.Range("B2", ws.Cells(lr, lc)).Value = ws.Range("B2", ws.Cells(lr, lc)).Value

Open in new window


I applied your function as follows:

ws.Range("B2", ws.Cells(lr, lc)).Formula = "=IF(ISNUMBER(MATCH(GetDigits($A2)&B$1,INDEX(tStatus[[Employee Number]:[Employee Number]]&tStatus[[Wk Number]:[Wk Number]],),0)),""Match"","""")"
ws.Range("B2", ws.Cells(lr, lc)).Value = ws.Range("B2", ws.Cells(lr, lc)).Value

Open in new window


I know my workbook did not include a letter prefix in the tStatus table, that was an error on my behalf when I posted the question.
0
aikimarkCommented:
If you have letter prefixes in your status worksheet, then create a new column with the getdigits() result.  Copy and paste-special (values) the new column.  Then you can delete the current.  This will clean up your status data.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.