1

I am looping thru column A of the ResultsTable only. Column A of the ResultsTable contains a list of FirstNames. If the row in column A of the ResultsTable has a value, then a XLOOKUP formula is applied to column B of the ResultsTable that matches the FirstName in the ResultsTable with the FirstName in the LookupTable. If the FirstName in the ResultsTable matches the FirstName in the LookupTable, then get the LastName associated with the FirstName from the LookupTable and put that LastName in column B of the ResultsTable. This should only be done if there is a FirstName found in column A of the ResultsTable (i.e. if "A2" has a FirstName value, then apply the formula to put the LastName from the LookupTable into cell "B2" of the ResultsTable). So the code below works but the formula is being applied to rows in column B of the ResultsTable when there isn't a FirstName in the row in column A of the ResultsTable (i.e. the code below is applying the formula to "B50" even though "A50" is blank). The number of rows in column A that contains a FirstName in the ResultsTable will be a dynamic range. How do I get the code below to a XLOOKUP formula in Column B of the ResultsTable ONLY if Column A of the ResultsTable has a FirstName value? Here is what the code looks like:

Sub Test()
    'Declare variables
    Dim wbk As Workbook, wsLookupTable As Worksheet, wsResultsTable As Worksheet 
    Dim rng As Range, FirstNames As Variant, i As Long
    'Set variables
    Set wbk = ThisWorkbook
    Set wsLookupTable = wbk.Worksheets("LookupTable")
    Set wsResultsTable = wbk.Worksheets("ResultsTable")
    'Activate the ResultsTable
    wsResultsTable.Activate
    With wsResultsTable
        'Set the Range to the UsedRange of Column A of the ResultsTable
        Set rng = wsResultsTable.UsedRange.Columns("A")
        'Set the FirstNames array to the Range of Column A of the ResultsTable
        FirstNames = rng
        'Use a For loop to search the Range in the ResultsTable
        For i = LBound(FirstNames, 1) To UBound(FirstNames, 1)
            'If FirstName in Column A has a value (i.e. not empty)
            If FirstNames(i, 1) <> "" Then
                'Use a XLOOKUP formula to match FirstName of ResultsTable
                'To the FirstName of the LookupTable
                'Put value of the LastName that matches the FirstName in the LookupTable
                'Into Column B of the ResultsTable
                rng(i, 2).FormulaR1C1 = _
                "=XLOOKUP([@FirstName],LookupTable[FirstName],LookupTable[LastName])"
            End If
        Next i
    End With
End Sub

Thanks a lot.

Jason McCoy
  • 63
  • 7
  • not knowing how column A was valued, I suggest a trim(FirstNames(i,1)) to ensure that spaces are ignored. Additionally either debug.print or stop the code before the loop to see the value of UBound(FirstNames, 1). – igittr Jan 02 '22 at 22:15
  • 1
    Is the table an actual Excel table and therefore potentially auto-filling formulas? – QHarr Jan 02 '22 at 22:35
  • Yes, they are actual Excel table and yes it is auto-filling the formula. How do I stop it from auto-filling the formula when there isn't a value in column A? And trimming the values in column A doesn't make a difference. – Jason McCoy Jan 02 '22 at 22:48
  • QHarr got me thinking about the solution and it was pretty easy in the end. All I had to do was disable Excel from auto-filling the formula by adding this line: ```Application.AutoCorrect.AutoFillFormulasInLists = False``` – Jason McCoy Jan 03 '22 at 01:36

1 Answers1

0

Add an IF statement to the formula to check if FirstName is blank or not. If it's blank, write a null string. If it's NOT blank, execute the XLOOKUP.

My syntax is likely wrong so please adjust accordingly.

rng(i, 2).FormulaR1C1 = "=IF([@FirstName] = """", """", XLOOKUP([@FirstName],LookupTable[FirstName],LookupTable[LastName]))"
Skin
  • 9,085
  • 2
  • 13
  • 29