If/Then/Else Loop

[jandb1998]

Here is what I would like to do:
Start on Row 2
If LastName, FirstName, City, State columns in Row 2 are the same as Row 3
BUT Row 2 Agent Code is different than Row 3 Agent Code,
THEN return the following in cell A18030:
Row 2 StateCode "-" Agent Code "/" Row 3 StateCode "-" AgentCode (example: 18-3130/18-2580)
THEN start on Row 3 and compare to Row 4, doing the same thing and if it is true the result would be returned to A18031.
It should move down the column comparing one row to the next row until it gets to the last row, returning result in 1 row down from previous result.
IF it gets to the last row (18023) and it has not found a match, then it should return "Sorry, None Found" n cell A18030.
There are 18023 rows.

Here are my columns:
B = StateCode
C = AgentCode
P = LastName
Q = FirstName
R = City
S = State

Here is the part I have so far,, but the looping has thrown me for a loop:
If (P2 = P3) Then
If (Q2 = Q3) Then
If (R2 = R3) Then
If (S2 = S3) Then
If (C2 <> C3) Then
B2 "-" + C2 + "/" + B3 + "-" + C3

I have been away from VB for so long I remember virtually nothing, so please be very specific in your response (where do I paste your code? "highlight "ThisWorkbook" on the left in the Project area, 1st dropdown on right = General, 2nd dropdown on right = Declarations")

Thank you so much for any help you can provide.

 

[Desu Nota from Columbus]

You can do this without a macro putting this formula in A18030 and dragging it down:

=IF(AND(P2=P3,Q2=Q3,R2=R3,S2=S3,C2<>C3),CONCATENATE(B2,"-",C2,"/",B3,"-",C3),"No Match Found")


Will this work or do you need a macro to do it?

 

[Desu Nota from Columbus]

Sub CONCVAL()

Dim r As Long
Dim lrow As Long

lrow = Range("B" & Rows.Count).End(xlUp).Row
For r = 2 To lrow
    If Cells(r, 16).Value = Cells(r + 1, 16).Value And Cells(r, 17).Value = Cells(r + 1, 17).Value And Cells(r, 18).Value = Cells(r + 1, 18).Value And Cells(r, 19).Value = Cells(r + 1, 19).Value And Cells(r, 3).Value <> Cells(r + 1, 3).Value Then
        Cells(r + 18028, 1).Formula = Cells(r, 2) & "-" & Cells(r, 3) & "/" & Cells(r + 1, 3) & "-" Cells(r + 1, 3)
    End If
Next r

End Sub

I have this looping until the last row of records in Column B. If this doesn't work, either let me know and I can change it, or you can change it your self by editing the third and fourth line of code.

 

[jandb1998]

I changed this part:
Cells(r + 18028, 1).Formula = Cells(r, 2) & "-" & Cells(r, 3) & "/" & Cells(r + 1, 3) & "-" Cells(r + 1, 3)

to this:
Cells(r + 18030, 1).Formula = Cells(r, 2) & "-" & Cells(r, 3) & "/" & Cells(r + 1, 2) & "-" Cells(r + 1, 3)

Where is the statement that gives me the "Sorry, None Found" in cell A18030 if it finds no matches?

Thanks.

 

[Desu Nota from Columbus]

I changed this part:
Cells(r + 18028, 1).Formula = Cells(r, 2) & "-" & Cells(r, 3) & "/" & Cells(r + 1, 3) & "-" Cells(r + 1, 3)

to this:
Cells(r + 18030, 1).Formula = Cells(r, 2) & "-" & Cells(r, 3) & "/" & Cells(r + 1, 2) & "-" Cells(r + 1, 3)

Where is the statement that gives me the "Sorry, None Found" in cell A18030 if it finds no matches?

Thanks.

By changing the range from Cells(r + 18028, 1) to Cells(r + 18030, 1) you are starting in A18032 instead of what you listed above.

Insert the following before End If

Else Cells(r + 18030, 1).Value = "Sorry, None Found"

 

[jandb1998]

(The formatting here is a little off, so please excuse that)
Ok, we are getting there
Here is what I have now but it is printing the "Sorry, None Found" for EACH comparison it is doing:

Sub MrExcelsFindMatchingAgentsLoop()
Dim r As Long
Dim lrow As Long

lrow = Range("B" & Rows.count).End(xlUp).Row
For r = 2 To lrow

If Cells(r, 16).Value = Cells(r + 1, 16).Value And Cells(r, 17).Value = Cells(r + 1, 17).Value And Cells(r, 18).Value = Cells(r + 1, 18).Value And Cells(r, 19).Value = Cells(r + 1, 19).Value And Cells(r, 3).Value <> Cells(r + 1, 3).Value Then
Cells(r + 18030, 1).Formula = Cells(r, 2) & "-" & Cells(r, 3) & "/" & Cells(r + 1, 2) & "-" & Cells(r + 1, 3)

Else: Cells(r + 18030, 1).Value = "Sorry, None Found"
End If
​

Next r

End Sub
****************************
How do I get it to only print "Sorry, None Found" if ALL comparisons are negative so I don't have to have another 18000+ rows?