"If A1='x' AND A1=A2, Then..." HELP!

[petegrant89]

Hello,
I am very new to programming macros. I am trying to write one that will search a column (Column A, for example), and check to see if two consecutive rows contain the same value. In this case, column A will be populated with either "2012" or "2013"; if there are two "2012" rows in a row, I need the macro to execute another macro (Macro1). If there are two "2013" rows in a row, I need the macro to execute yet another macro (Macro2). I will paste what I managed to achieve down below, the problem is that if the condition (two Consecutive 2012 or 2013 rows) is met in Row 1, it stops searching the rest of the document. Any help with this would be greatly appreciated.

Sub test()
For x = 1 To 8000
If Range("A1") = "2012" And Range("A1") = Range("A2") Then
Application.Run "Book1!Macro1"
Else
End If
If Range("A1") = "2013" And Range("A1") = Range("A1") Then
Application.Run "Book1!Macro2"
Else
End If
Next x
End Sub

 

[JackDanIce]

Welcome to the board.

Try:

Sub test()

Dim x as Long


For x = 1 To Range("A" & Rows.Count).End(xlUp).Row
  If Range("A" & x) = Range("A" & x + 1) Then
     If Range("A" & x) = "2012" Then Application.Run "Book1!Macro1"
     If Range("A" & x) = "2013" Then Application.Run "Book1!Macro2"
  End If
Next x

End Sub

The bit in red checks if your last row is 8000 or not. Change it back to 8000 if you prefer

What happens if A1 = A2 (= 2012) and then A2 = A3?

 

[VoG]

Maybe like this

Sub test()
For x = 1 To 8000
    If Range("A" & x) = "2012" And Range("A" & x) = Range("A" & x + 1) Then
        Application.Run "Book1!Macro1"
    End If
    If Range("A" & x) = "2013" And Range("A" & x) = Range("A" & x + 1) Then
        Application.Run "Book1!Macro2"
    End If
Next x
End Sub

 

[mikerickson]

For x =1 to 8000
    Select Case Cells(1, x) & "," & Cells(1, x+1)
        Case "2012,2012"
            Call Macro1
        Case "2013,2013"
            Call Macro2
    End Select
Next x

 

[petegrant89]

Thank you very much for your responses. Ive written a macro that adds a row above a row and fills in the added row with data that it obtains from different parts of the file. The problem was that I needed it to copy different information depending on if it was adding a row between two "2012" values or two "2013" values. The macro you posted up top should enable me to do that.

 

[petegrant89]

One followup question: If I want this "test()" macro to keep running through the cells even after it executes "macro1()" or "macro2()", must I incorporate the line Application.Run "Book1!test" at the end of the "macro1()" and "macro2()" coding, or will the "test()" macro automatically search through the whole document, executing "macro1()" and "macro2()" when necessary?

Thanks again

 

[mikerickson]

No change is needed to Macro1 or Macro2.

When their code hits an End Sub or Exit Sub line, execution will return to where it was when the Sub was called, i.e. inside Tests's loop.

Why do you ask? In your testing, did it miss any of the cells after the first?

 

[petegrant89]

No, but the loop is just a small part of a bigger design, so I have not been able to test it on a full scale yet; I just wanted to double check before I got to that part.

Thanks for your help,