Loop 2 Ranges - "Invalid Next Control Variable Reference"

[Sphinx404]

In layman terms, what I'm trying to do:

If "V2" = "A2" then
"V2" = "True"
Else
"V2" = "False"

Then proceeding to the next row in both ranges, all the way to the bottom of the data set.

Sub Step6()


[COLOR=#ff0000]Dim mycell As Range[/COLOR]
[COLOR=#ff0000]Dim myrange As Range[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]For Each mycell In Range("V2", Range("V" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#ff0000]For Each myrange In Range("A2", Range("A" & Rows.Count).End(xlUp)[/COLOR])


Select Case Range(mycell).Value
    Case "ATG"
        If Range(myrange).Value = "ATG" Then
            Range(mycell).Value = "True"
        Else
            Range(mycell).Value = "False"
        End If
End Select


[COLOR=#ff0000]Next mycell[/COLOR]
[COLOR=#ff0000]Next myrange[/COLOR]


End Sub

Not sure the coding highlighted in RED is absolutely necessary, seems this can be done in a more simple format. Any help is appreciated.

 

[RoryA]

It should look like this, with your two Next statements switched, and you a couple of other changes:

Sub Step6()


Dim mycell As Range
Dim myrange As Range


For Each mycell In Range("V2", Range("V" & Rows.Count).End(xlUp))
For Each myrange In Range("A2", Range("A" & Rows.Count).End(xlUp))


Select Case mycell.Value
    Case "ATG"
        If myrange.Value = "ATG" Then
            mycell.Value = "True"
        Else
            mycell.Value = "False"
        End If
End Select

Next myrange
Next mycell



End Sub

 

[Sektor]

Could be simplified further: mycell.Value = IIf(myrange.Value = "ATG", "True", "False")

 

[Sphinx404]

It should look like this, with your two Next statements switched, and you a couple of other changes:

Sub Step6()


Dim mycell As Range
Dim myrange As Range


For Each mycell In Range("V2", Range("V" & Rows.Count).End(xlUp))
For Each myrange In Range("A2", Range("A" & Rows.Count).End(xlUp))


Select Case mycell.Value
    Case "ATG"
        If myrange.Value = "ATG" Then
            mycell.Value = "True"
        Else
            mycell.Value = "False"
        End If
End Select

Next myrange
Next mycell



End Sub

@Rory, thank you! That worked very well. Now that you have corrected my mistake, I've gone ahead and added a few more parameters as shown below.

A couple questions:
1. Why won't my "LIKE" statements work?
I put the * in front and/or in back of what I'm trying to find so it accounts for characters in the string, but unless it's an exact match, it returns FALSE. I thought putting the * meant it would locate what i'm looking for regardless of where it's located in the string.
2. This macro takes forever to run (about 45-60 seconds), and I only have 1947 rows. Why? How do I simplify it?
Some of my reports could be 80,000+ rows. I tried using @Sektor solution below, but it returns all "True" values.

Any ideas?

Thanks Rory and Sektor, your help is extremely valuable. I just decided to start learning VBA about 6 days ago, so all comments are very helpful.

Sub Step6()


Dim mycell As Range
Dim myrange As Range


For Each mycell In Range("V2", Range("V" & Rows.Count).End(xlUp))
For Each myrange In Range("A2", Range("A" & Rows.Count).End(xlUp))


Select Case True
    Case mycell.Value = "ATG"
        If myrange.Value Like "AT*" Then
            mycell.Value = "True"
        Else
            mycell.Value = "False"
        End If
    Case mycell.Value = "MSP"
        If myrange.Value Like "*MSP*" Then
            mycell.Value = "True"
        Else
            mycell.Value = "False"
        End If
    Case mycell.Value = "DTW"
        If myrange.Value Like "*DTW*" Then
            mycell.Value = "True"
        Else
            mycell.Value = "False"
End Select


Next myrange
Next mycell


End Sub