Run-Time Error 13, error 2042

Results 1 to 3 of 3

Thread: Run-Time Error 13, error 2042

  1. #1
    Board Regular
    Join Date
    May 2015
    Location
    In the Woods
    Posts
    141
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Run-Time Error 13, error 2042

     
    I've been through this forum front and back and taking the suggestions from previous posts about this error has given me a lot of knowledge concerning this error, but the error persists nonetheless. Does someone see something I can be doing differently to stop this error from occurring?

    Code:
    Sub step10()
    
    
    ActiveWorkbook.Sheets("HazShipper").Select
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    
    Dim row As Long 'CHANGE FROM DOUBLE TO LONG
    Dim code1 As String
    Dim code2 As String
    Dim code3 As String
    
    
    Dim match1 As String
    Dim match2 As String
    Dim match3 As String
    
    
    row = 2
    
    
    Range("AD2").Formula = "=VLOOKUP(C2,IMP.SPL!$A$2:$D$44,2,0)"
    Range("AE2").Formula = "=VLOOKUP(C2,IMP.SPL!$A$2:$D$44,3,0)"
    Range("AF2").Formula = "=VLOOKUP(C2,IMP.SPL!$A$2:$D$44,4,0)"
    
    
    Range("AG2").Formula = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,16,0),)"
    Range("AH2").Formula = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,17,0),)"
    Range("AI2").Formula = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,18,0),)"
    Range("AJ2").Formula = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,19,0),)"
    Range("AK2").Formula = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,20,0),)"
    
    
    Range("AD2:AK2").Select
    Selection.AutoFill Destination:=Range("AD2:AK25000")
    
    
    Do Until Range("AD" & row).Formula = ""
    
    
    match1 = ""
    match2 = ""
    match3 = ""
    
    
    code1 = Range("AD" & row).Value 'code1="RFL" range = error 2042
    'code shuts down when faced with #N/A.... but I have IFNA accounted for in formula..... What gives?
    code2 = Range("AE" & row).Value
    code3 = Range("AF" & row).Value
    
    
    If code1 = Range("AG" & row).Value Then match1 = "MATCH"
    If code1 = Range("AH" & row).Value Then match1 = "MATCH"
    If code1 = Range("AI" & row).Value Then match1 = "MATCH"
    If code1 = Range("AJ" & row).Value Then match1 = "MATCH"
    If code1 = Range("AK" & row).Value Then match1 = "MATCH"
    
    
    If code2 = Range("AG" & row).Value Then match2 = "MATCH"
    If code2 = Range("AH" & row).Value Then match2 = "MATCH"
    If code2 = Range("AI" & row).Value Then match2 = "MATCH"
    If code2 = Range("AJ" & row).Value Then match2 = "MATCH"
    If code2 = Range("AK" & row).Value Then match2 = "MATCH"
    
    
    If code3 = Range("AG" & row).Value Then match3 = "MATCH"
    If code3 = Range("AH" & row).Value Then match3 = "MATCH"
    If code3 = Range("AI" & row).Value Then match3 = "MATCH"
    If code3 = Range("AJ" & row).Value Then match3 = "MATCH"
    If code3 = Range("AK" & row).Value Then match3 = "MATCH"
    
    
    If match1 = "MATCH" Then
        Range("AL" & row).Value = "MATCHES"
    Else
        If match2 = "MATCH" Then
            Range("AL" & row).Value = "MATCHES"
        Else
            If match3 = "MATCH" Then
                Range("AL" & row).Value = "MATCHES"
            Else
                Range("AL" & row).Value = "NO MATCHES"
            End If
        End If
    End If
    row = row + 1
    
    
    Loop
    
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    
    End Sub
    
    Last edited by Sphinx404; Nov 22nd, 2017 at 02:26 PM.
    MS 2010, 2013, 2016 (seldom)
    Win 7

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,841
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-Time Error 13, error 2042

    I don't see anything in the formula in AD2 that accommodates an NA error.
    Last edited by shg; Nov 22nd, 2017 at 02:38 PM.

  3. #3
    Board Regular
    Join Date
    May 2015
    Location
    In the Woods
    Posts
    141
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-Time Error 13, error 2042

      
    Quote Originally Posted by shg View Post
    I don't see anything in the formula in AD2 that accommodates an NA error.
    As you guys usually are... you are correct. I did not have IFNA added to my first set of formulas, only the second set.
    MS 2010, 2013, 2016 (seldom)
    Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com