VBA for Replacing #N/A with Value from an Offset Cell in Multiple Columns

[FrenchCelt]

Hello,

I have a macro that uses VLOOKUP to pull values from an older version of a spreadsheet on a newer version to look for differences in the values. I then filter for any differences over 0. It works perfectly if there is an exact 1:1 match for the VLOOKUP values. Sometimes, though, the newer data will have a value that isn't in the original data, and the VLOOKUP will leave behind #N/A.

So, I need some code that will look for #N/A in Columns L and M and replace with the offset value from the same row in Column J. For example:

Rank Final Incentive

FW User JF Concatenate

Login User Id

Fiscal Week

Emp ID

Autolink File Num

Pay Grp

Earning Code

Incentive Job Function

Final Incentive Payout

Original

Difference

17aboydBAG PICK

aboyd

17

BAG PICK

$25.21

#N/A​

#N/A​

Should look like this:

Rank Final Incentive

FW User JF Concatenate

Login User Id

Fiscal Week

Emp ID

Autolink File Num

Pay Grp

Earning Code

Incentive Job Function

Final Incentive Payout

Original

Difference

17aboydBAG PICK

aboyd

17

BAG PICK

$25.21

$25.21

$25.21

Can anyone help?

 

[Alex Blakenburg]

Do you just mean ?

=iferror(YourVlookup,$J2)

 

[FrenchCelt]

How would I plug that into my code?

This is the original VLOOKUP part of the code:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],C[3]:C[4],2,FALSE)"

 

[Alex Blakenburg]

Try this:

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-10],C[3]:C[4],2,FALSE),RC10)"

 

[FrenchCelt]

Ok, that works in the VLOOKUP section for Column L, but Column M is based on this formula:

ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-1]"

How can I adapt to do the same thing?

 

[Alex Blakenburg]

You are using relative addressing which makes it really hard to work out what columns you want any amendments to use.
If you want the above modified with If error what column do you want to return the value from ?

 

[FrenchCelt]

Column J, just like with the first case.

 

[Alex Blakenburg]

Then same format as before.

 ActiveCell.FormulaR1C1 = "=IFERROR(RC[-3]-RC[-1], RC10)"

 

[FrenchCelt]

Ah, unfortunately by fixing the #N/A issue at the VLOOKUP phase, I end up with a zero in Column M, because the value in Column L now matches what is in Column J. So I need the error correction part to be done after VLOOKUP in Column L and the subtraction formula in Column M.

 

[FrenchCelt]

I figured out a solution from doing some Googling. Here is what I came up with:

Columns("L:L").Select
    Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

    
    Dim NARange As Range, CheckCell As Range

    Set NARange = Range("M2:M1000")

    For Each CheckCell In NARange
    If Application.WorksheetFunction.IsNA(CheckCell) Then
         CheckCell.Offset(0, -3).Copy
         CheckCell.Select
         ActiveSheet.Paste
    End If
    Next CheckCell

I realized that I actually needed to make the value in Column L a zero because otherwise it wouldn't make sense to claim that the value in Column M has a difference compared to Column J.