title: Why is formula being used on blank cells in a range?
body: <p>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 &quot;A2&quot; has a FirstName value, then apply the formula to put the LastName from the LookupTable into cell &quot;B2&quot; 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 &quot;B50&quot; even though &quot;A50&quot; 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:</p>
  <pre><code>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(&quot;LookupTable&quot;)
      Set wsResultsTable = wbk.Worksheets(&quot;ResultsTable&quot;)
      'Activate the ResultsTable
      wsResultsTable.Activate
      With wsResultsTable
          'Set the Range to the UsedRange of Column A of the ResultsTable
          Set rng = wsResultsTable.UsedRange.Columns(&quot;A&quot;)
          '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) &lt;&gt; &quot;&quot; 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 = _
                  &quot;=XLOOKUP([@FirstName],LookupTable[FirstName],LookupTable[LastName])&quot;
              End If
          Next i
      End With
  End Sub
  </code></pre>
  <p>Thanks a lot.</p>