Get list of items that cause Vlookup failure and error

Question

I am working with family history data and need to ascertain the Sex from the Given Name.

I built a lookup table of given names and usual Sex but it occasionally fails due to a missing or unrecognized lookup entry.

I normally Debug.Print the row number on failure and manually make an entry to the lookup table.

I have been investigating the use of On Error to ensure my code completes. I would like to get a List of failed entries so I can manually add a M or F to the table.

The lookup is below.

Set rng = Worksheets("Given_Names").Columns("A:A").Find(What:=sGiven, _
  LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
iLookUpRow = rng.Row
sSex = Worksheets("Given_Names").Cells(iLookUpRow, 2).Value

You may check the example in the documentation how to can check if find has find something. — Shrotter
– Shrotter, Commented Jan 26 at 17:50

taller · Accepted Answer · 2025-01-26 18:42:41Z

If sGiven does not exist in the lookup table, rng will be Nothing, causing iLookUpRow = rng.Row to raise a runtime error 91.

The sample script highlights the specified name that is missing from the lookup table.

Sub Demo()
    Dim rng As Range, sMissing As String, c As Range
    Dim iLookupRow As Long, sSex As String, sGiven As String
    Dim iRow As Long: iRow = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    If iRow <= 2 Then Exit Sub
    For Each c In Worksheets("Sheet1").Range("A2:A" & iRow)
        sGiven = c.Value
        Set rng = Nothing
        Set rng = Worksheets("Given_Names").Columns("A:A").Find(What:=sGiven, _
            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If rng Is Nothing Then
            c.Interior.Color = vbYellow
        Else
            iLookupRow = rng.Row
            sSex = Worksheets("Given_Names").Cells(iLookupRow, 2).Value
            c.Offset(0, 2) = sSex
        End If
    Next
End Sub

Black cat · Accepted Answer · 2025-01-27 09:55:51Z

0

Replace the sGiven= line with some input e.g. an InputBox or call the sub with a parameter to assign a value for the variable.
It will popup an input box for the valid sex if it is missing in column B.

Sub nosex()
Set ws = Worksheets("Given_Names")
sGiven = "KATE"  'replace this line with some input source
Set rng = ws.Columns("A:A").Find(What:=sGiven, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng.Offset(0, 1) = "" Then
    Do While Not UCase(inpsex) Like "[FM]"
        inpsex = Application.InputBox("What is the sex of " & sGiven & "?", "Not typed")
        If TypeName(inpsex) = "Boolean" Then Exit Sub
    Loop
    Cells(WorksheetFunction.Match(sGiven, ws.Columns("A:A"), 0), 2) = UCase(inpsex)
End If
iLookUpRow = rng.Row
sSex = ws.Cells(iLookUpRow, 2).Value
End Sub

answered Jan 27 at 9:55

Black cat

7,6358 gold badges69 silver badges99 bronze badges

1 Comment

Jompra Jan 27 at 14:45

Thank you both for those suggestions which were a giant leap forward of my basic writings. I can study these methods more, make my own notes and step forward a little in my learnings. Thank you again for taking time to reply.

Collectives™ on Stack Overflow

Get list of items that cause Vlookup failure and error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related