CODEWITHSUNDEEP
vbaexcelif-statement
Paul NoCal
Paul NoCal

Reputation: 77

Else Condition prevents If Condition from executing in VBA

I am trying to run the following code in Excel VBA but the Else condition mistakenly overrides the If condition. As a result, all every j record is identified as a No match. Yet, when I remove the Else condition, then the If condition runs properly and matches the j records to the appropriate i records.

For j = 1 To 27
For i = 1 To 37
    If Dec1998(i) = DecGIS(j) And Stream1998(i) = StreamGIS(j) And Trib1998(i) = TribGIS(j) Then
        ID_GIS(j) = Rec1998(i)
    Else 
        ID_GIS(j) = "No match"
    End If
Next i
Next j

For brevity, I have included only a sub-section of the entire code in the question. For clarity, I have attached screenshots of:

  1. j records (results with Else condition)
  2. i records

Due to my lack of reputation points, I cannot upload any more images; otherwise I would provide the j records without the Else condition as well as a screenshot of the entire VBA code.

How can I modify my code so that the Else condition identifies the j records as "no match" ONLY when no corresponding i record exists?

Upvotes: 0

Views: 113

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Put it before the inner loop and exit inner loop after match is found.

This way "No Match" is the default and is overwritten if a match is found.

For j = 1 To 27
    ID_GIS(j) = "No match"
    For i = 1 To 37
        If Dec1998(i) = DecGIS(j) And Stream1998(i) = StreamGIS(j) And Trib1998(i) = TribGIS(j) Then
            ID_GIS(j) = Rec1998(i)
            Exit For                           
        End If
    Next i
Next j

Upvotes: 4

Related Questions