Exiting a For Loop when a condition in an If Elseif loop is met

Erik1990

New Member
Joined
Sep 16, 2014
Messages
1
Hello,

I am trying to write an If ElseIf loop inside of a For loop. I want the code to look through multiple rows in and match cells in column B and column C if it is there. If it finds that both columns match, I want it to display the values in the next two available columns. If the data does not match, I want it to add the data to the next available row. See the code below.

Code:
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Cpk As Double
Dim Total As Integer
Dim b As Integer
Dim CC As Integer
Dim Due As Date

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Tracking")
    
Shop = ws1.Range("B2")
ID = ws1.Range("B3")
Day = ws1.Range("B4")
Cpk = ws1.Range("E5")
Total = ws2.Range("C1")

For b = 0 To Total
    If ws2.Cells(3 + b, 2) = Shop And ws2.Cells(3 + b, 3) = ID Then
        CC = ws2.Cells(3 + b, 1)
        ws2.Cells(3 + b, CC + 2) = Day
        ws2.Cells(3 + b, CC + 3) = Cpk
        Due = DateAdd("yyyy", 1, Day)
        ws2.Cells(3 + b, 4) = Due
        Exit For
    ElseIf ws2.Cells(3 + b, 2) <> Shop Or ws2.Cells(3 + b, 3) <> ID Then
        ws2.Cells(3 + Total, 2) = Shop
        ws2.Cells(3 + Total, 3) = ID
        ws2.Cells(3 + Total, 5) = Day
        ws2.Cells(3 + Total, 6) = Cpk
        Due = DateAdd("yyyy", 1, Day)
        ws2.Cells(3 + Total, 4) = Due
        Exit For
    End If
Next b

The code works as I want it to the first time it is executed and as long as I don't change the Shop and ID, it places them in the same row and corresponding columns if it is executed multiple times. The problem comes when I change the Shop or ID. It then places the correct data in the columns, but seems to loop through the if statement an extra time and places a 2nd data set in the next columns over of the same row. When I execute the code again, it doesn't register that the Shop and ID match and places it in the next empty row, but without looping through the if statement a second time. So there is only one set of data in every row when it is executed. If I change the Shop and ID to match the original, which is the first line of this table, the code works as I think it should. So the code works for the first line in the sheet, but nowhere else and that has me really confused.

I've tried multiple variations of the code above including:
+taking the "exit for" statement out of the elseif portion of the if statement, but that doesn't work correctly either as it adds a new row but also adds data to the previous row.
+making the "elseif" just an "else" since I think that should also do what I want
+removing the "exit for" statements completely
+I've tried making the if statements separate so:
Code:
For b = 0 To Total Step 1
    If ws2.Cells(3 + b, 2) = Shop Then
    If ws2.Cells(3 + b, 3) = ID Then
        CC = ws2.Cells(3 + b, 1)
        ws2.Cells(3 + b, CC + 2) = Day
        ws2.Cells(3 + b, CC + 3) = Cpk
        Due = DateAdd("yyyy", 1, Day)
        ws2.Cells(3 + b, 4) = Due
        Exit For
    End If
    End If
    
    If ws2.Cells(3 + b, 2) <> Shop Then 
    If ws2.Cells(3 + b, 3) <> ID Then
        ws2.Cells(3 + Total, 2) = Shop
        ws2.Cells(3 + Total, 3) = ID
        ws2.Cells(3 + Total, 5) = Day
        ws2.Cells(3 + Total, 6) = Cpk
        Due = DateAdd("yyyy", 1, Day)
        ws2.Cells(3 + Total, 4) = Due
        Exit For
    End If
    End If
Next b
This code worked, unless I changed the ID and then it wouldn't recognize that there was already an entry that it should be matching.

This is being done in excel 2007 on windows 7. Any help on this would be greatly appreciated!

P.S. I'm what you would call a beginner at vba coding and am trying to teach myself based on googling questions, so the code above may not be the most efficient way to do things.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,222,009
Messages
6,163,381
Members
451,834
Latest member
tomtownson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top