VLookUP- not picking up the double digit number except numbers preceding with a zero.

[Livin404]

Greetings,

I put the workbook in the Dropbox, the best way to capture the full picture. I changed the table to names, These names are random as I change the text data. I provided some random mission numbers with a couple of notes. It is picking up "Most" of the codes, but it is missing a few. Of course I get the mismatch error. If you look at the first Column you will see there are single digits, double digits. I know the I am going to have an issue of VLOOKup going to the first digit beginning with the same letter. It is also not recognizing double digit numbers. Maybe Match would work better, but I have no experience with Match.

Find Mission change.xlsm

Sub VLOOK_UP()
Dim alphaCode As String, n As Variant
 With Worksheets("MSN Decoder")
        alphaCode = .Range("K6").Value
        n = Mid(alphaCode, 4, 2)  ''' extracts the 4th and 5th characters
                ''' If one character is a letter and the other is a number then choose single character
        ''' otherwise choose double characters
        If IsNumeric(Left(n, 1)) And Not IsNumeric(Right(n, 1)) Then
                n = Left(n, 1)
        ElseIf Not IsNumeric(Left(n, 1)) And IsNumeric(Right(n, 1)) Then
                n = Left(n, 1)
        End If
        ''' Paste/insert the output value
        .Range("H10").Value = Application.WorksheetFunction.VLookup(n, Worksheets("Operator").Range("A:B"), 2, 0)
 End With
  End Sub

Thank you,

 

[Rick Rothstein]

I only skimmed your code, but this is what I thought needed attention...

This is your ElseIf block from your VLook_Up subroutine...

ElseIf Not IsNumeric(Left(n, 1)) And IsNumeric(Right(n, 1)) Then
n = Left(n, 1)

Shouldn't the n=Left(n,1) be n=Right(n,1) instead?

Also, where in your code to you retrieve a two-digit value? With the change above, you would only retrieve one digit, the right one or the left one. I think you need to expand your If..Then block to include a test to see if both characters are digits... and that should be the first test that is done.

 

[Alex Blakenburg]

Your main issue is that you have a mix of numbers and text in column A of your operator sheet.
Having both single and double digit versions of the same value is also concerning although not a show stopper eg 01 & 1 exist as do 02 & 2 etc

Ideally the data is cleaned up before it gets to your VLOOK_UP but on the assumption that it isn't the below should fix it.
It does mean you will be constantly doing the conversion which is overkill.
So you should be looking to move the TextToColumns line, to a sub that runs earlier in the process and that only runs after the data has been refreshed.

I have made the change @Rick Rothstein suggested [Right(n,1)].

@Rick Rothstein - n is set to 2 characters initially and if the other 2 conditions fail stays unchanged as 2 digits for the lookup.

Dim alphaCode As String, n As Variant

    ' Fix Data type discrepancy - mixture of numeric and text - convert all to text
    With Worksheets("Operator")
        .Range("A:A").TextToColumns Destination:=.Range("A1"), _
            DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
            FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
    End With

 With Worksheets("MSN Decoder")

        alphaCode = .Range("K6").Value

        n = Mid(alphaCode, 4, 2)  ''' extracts the 4th and 5th characters
        
        ''' If one character is a letter and the other is a number then choose single character

        ''' otherwise choose double characters

        If IsNumeric(Left(n, 1)) And Not IsNumeric(Right(n, 1)) Then

                n = Left(n, 1)

        ElseIf Not IsNumeric(Left(n, 1)) And IsNumeric(Right(n, 1)) Then

                n = Right(n, 1)
        End If

        ''' Paste/insert the output value
        
        ' Removed WorksheetFunction to allow addition of IfError
        With Application
            .Range("H10").Value = .IfError(.VLookup(n, Worksheets("Operator").Range("A:B"), 2, 0), "")
        End With
 
End With
  
End Sub

 

[Livin404]

Your main issue is that you have a mix of numbers and text in column A of your operator sheet.
Having both single and double digit versions of the same value is also concerning although not a show stopper eg 01 & 1 exist as do 02 & 2 etc

Ideally the data is cleaned up before it gets to your VLOOK_UP but on the assumption that it isn't the below should fix it.
It does mean you will be constantly doing the conversion which is overkill.
So you should be looking to move the TextToColumns line, to a sub that runs earlier in the process and that only runs after the data has been refreshed.

I have made the change @Rick Rothstein suggested [Right(n,1)].

@Rick Rothstein - n is set to 2 characters initially and if the other 2 conditions fail stays unchanged as 2 digits for the lookup.

Dim alphaCode As String, n As Variant

    ' Fix Data type discrepancy - mixture of numeric and text - convert all to text
    With Worksheets("Operator")
        .Range("A:A").TextToColumns Destination:=.Range("A1"), _
            DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
            FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
    End With

 With Worksheets("MSN Decoder")

        alphaCode = .Range("K6").Value

        n = Mid(alphaCode, 4, 2)  ''' extracts the 4th and 5th characters
       
        ''' If one character is a letter and the other is a number then choose single character

        ''' otherwise choose double characters

        If IsNumeric(Left(n, 1)) And Not IsNumeric(Right(n, 1)) Then

                n = Left(n, 1)

        ElseIf Not IsNumeric(Left(n, 1)) And IsNumeric(Right(n, 1)) Then

                n = Right(n, 1)
        End If

        ''' Paste/insert the output value
       
        ' Removed WorksheetFunction to allow addition of IfError
        With Application
            .Range("H10").Value = .IfError(.VLookup(n, Worksheets("Operator").Range("A:B"), 2, 0), "")
        End With
 
End With
 
End Sub

That is incredible! Every thing seems to work brilliantly, importantly no more errors, and it is picking up the double numeric digit. The only thing now is if will not pick up if I need to use the single letter found in position 4. In other words, if the look up does not find a match for the fourth and fifth digit it cannot detect if there is a letter in position 4. If the fourth position is a number then it works fine, if they are both letters it works, if it's a number and letter it works fine. Only if there is a letter followed by a number it does not work. Thank you, we're nearly there.

 

[Alex Blakenburg]

Can you give us an example of the full code in the data entry box and what you want it to find.

 

[Livin404]

Can you give us an example of the full code in the data entry box and what you want it to find.

Thank you, I provided another link to the Dropbox which includes your updated Macro. I provided several examples.

This is so close now.

The link is https://www.dropbox.com/scl/fi/zskpvj9v3nz1fu13cbqc8/Find-Mission-change.xlsm?dl=0&rlkey=cxp2qzypbeosraopd64q4qy2k

 

[Alex Blakenburg]

The logic is a little different to what we thought it was. If I understand it correctly where n = 4th & 5th characters.
• Pos 4 is number and Pos 5 is not use single digit
• Pos 4 is alpha and Pos 5 is number - this 2 character combination of alpha-digit will never exist so just use the Pos 4 alpha character (changed back to being Left)
• Both characters are numbers use both characters
• Both characters are alpha - try using both and if that doesn't exist use just the 1st alpha character (changed try both characters first then use only 1)

Try the code below:

Sub VLOOK_UP()

Dim alphaCode As String, n As Variant
Dim try_VLook As String

    ' Fix Data type discrepancy - mixture of numeric and text - convert all to text
    With Worksheets("Operator")
        .Range("A:A").TextToColumns Destination:=.Range("A1"), _
            DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
            FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
    End With

 With Worksheets("MSN Decoder")
        try_VLook = ""
        alphaCode = .Range("K6").Value

        n = Mid(alphaCode, 4, 2)  ''' extracts the 4th and 5th characters
        
        ''' If one character is a letter and the other is a number then choose single character

        ''' otherwise choose double characters

        If IsNumeric(Left(n, 1)) And Not IsNumeric(Right(n, 1)) Then

                n = Left(n, 1)

        ElseIf Not IsNumeric(Left(n, 1)) And IsNumeric(Right(n, 1)) Then
                ' No Codes with alpha in the 1st position and digit in the 2nd position are used
                n = Left(n, 1)
        ElseIf Not IsNumeric(Left(n, 1)) And Not IsNumeric(Right(n, 1)) Then
            ' if both characters alpha - try both characters
            With Application
                try_VLook = .IfError(.VLookup(n, Worksheets("Operator").Range("A:B"), 2, 0), "")
            End With
            ' if not found use left character only
            If try_VLook = "" Then
                n = Left(n, 1)
            End If
        End If

        ''' Paste/insert the output value
        
        ' Removed WorksheetFunction to allow addition of IfError
        If try_VLook = "" Then
            With Application
                .Range("H10").Value = .IfError(.VLookup(n, Worksheets("Operator").Range("A:B"), 2, 0), "")
            End With
        Else
            .Range("H10").Value = try_VLook
        End If
 
End With
  
End Sub

 

[Livin404]

The logic is a little different to what we thought it was. If I understand it correctly where n = 4th & 5th characters.
• Pos 4 is number and Pos 5 is not use single digit
• Pos 4 is alpha and Pos 5 is number - this 2 character combination of alpha-digit will never exist so just use the Pos 4 alpha character (changed back to being Left)
• Both characters are numbers use both characters
• Both characters are alpha - try using both and if that doesn't exist use just the 1st alpha character (changed try both characters first then use only 1)

Try the code below:

Sub VLOOK_UP()

Dim alphaCode As String, n As Variant
Dim try_VLook As String

    ' Fix Data type discrepancy - mixture of numeric and text - convert all to text
    With Worksheets("Operator")
        .Range("A:A").TextToColumns Destination:=.Range("A1"), _
            DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
            FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
    End With

 With Worksheets("MSN Decoder")
        try_VLook = ""
        alphaCode = .Range("K6").Value

        n = Mid(alphaCode, 4, 2)  ''' extracts the 4th and 5th characters
       
        ''' If one character is a letter and the other is a number then choose single character

        ''' otherwise choose double characters

        If IsNumeric(Left(n, 1)) And Not IsNumeric(Right(n, 1)) Then

                n = Left(n, 1)

        ElseIf Not IsNumeric(Left(n, 1)) And IsNumeric(Right(n, 1)) Then
                ' No Codes with alpha in the 1st position and digit in the 2nd position are used
                n = Left(n, 1)
        ElseIf Not IsNumeric(Left(n, 1)) And Not IsNumeric(Right(n, 1)) Then
            ' if both characters alpha - try both characters
            With Application
                try_VLook = .IfError(.VLookup(n, Worksheets("Operator").Range("A:B"), 2, 0), "")
            End With
            ' if not found use left character only
            If try_VLook = "" Then
                n = Left(n, 1)
            End If
        End If

        ''' Paste/insert the output value
       
        ' Removed WorksheetFunction to allow addition of IfError
        If try_VLook = "" Then
            With Application
                .Range("H10").Value = .IfError(.VLookup(n, Worksheets("Operator").Range("A:B"), 2, 0), "")
            End With
        Else
            .Range("H10").Value = try_VLook
        End If
 
End With
 
End Sub

For what I've seen so far it works perfectly. I want to thank you from the bottom of my heart. I have other things I'm working with these alpha numeric numbers, but it appears be great. I genuinely appreciate it. 10 Stars for you!

 

[Alex Blakenburg]

Thank and you are welcome. Glad we could help.