CODEWITHSUNDEEP
vbaexcelexcel-formula
TurboCoder
TurboCoder

Reputation: 1011

VBA - IsNumber Returns Nothing Due to Prefix

Follow Up Question from:

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 account for a 2 letter prefix in front of the number.

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

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.

Link to a sample file:

https://drive.google.com/file/d/1Uoa0Yn72nSckQaBnl6Y-a2q6UMJX6H3f/view?usp=sharing

Upvotes: 0

Views: 85

Answers (1)

learnAsWeGo
learnAsWeGo

Reputation: 2282

this formula tells you if there is 2,1 or 0 non number prefixes. You can use the number that this formula produces to cut the prefixes off of the number and then conduct your match.

Formula 1:

=IF(ISNUMBER(NUMBERVALUE(LEFT(B2,1))),0,IF(ISNUMBER(NUMBERVALUE(RIGHT(LEFT(B2,2),1))),1,2))

If the first char on the from left of the string is a number, return 0, if it is not check if the first char from the right of the string formed when taking the first two chars from the left of the string is a number, if that too is a number then return 2, else 1

Formula 2:

=RIGHT(B2,LEN(B2)-C2)

Upvotes: 1

Related Questions