I am trying to figure out how to determine if the VLOOKUP formula returns #N/A. I want to find what the formula returns, if it is NOT
#N/A, then go to the next cell.
If it IS
#N/A, then I want to:
1.Copy cell A (and the row number the #N/A occurs) as well as cell C of the same row.
In my example, that would be A7 and C7.
2. Paste these values into Sheets(Corrected ID-Name), at the first open row at the bottom
In my example, that would be row 7 on corrected ID-Name.
3. Hopefully B8 on Sheet1, will automatically update the Vlookup formula, if not, do I need to say “update calculations, then go on to next cell”?
I understand that the names on Sheet1 column C, are different for the same ID Number, the first one will be copied, whatever that maybe and then from now on, that is what that ID will refer to.
Code and example workbook attached
Dim cell As Range
Dim rng As Range
' Run with Sheet1 as active sheet
ActiveSheet.Range("B1").Value = "VL"
ActiveSheet.Range("B2").FormulaR1C1 = _
Set rng = [B2]
Set rng = Range(rng, Cells(Cells(Rows.Count, 1).End(xlUp).Row, rng.Column))
If rng.Rows.Count = 1 Then
rng.Cells(1, 1).AutoFill Destination:=rng, Type:=xlFillDefault
For Each cell In rng
If cell = "#N/A" Then
MsgBox "Found an #N/A"
'copy cell to left and copy cell to right
'paste onto Correct Agent ID-Name
'Using first open row at bottom
'Will vlookup update so that repeated #N/A's are not copyied again?
'If not, do I need to say "recalculate" prior to going on to next cell looking for #N/A
MsgBox "Not a #N/A"