For loop in vba question?

[tahir9]

In my code I have three loops however the final two loops I want to be transitioning at the same time, so instead of next i or for it to work properly it should be next i and next z but it seems I can't do that is there a way around that... or a simpler solution that's not hitting me yet? Here is my code:

Sub dateofvisit()
Dim lastrow As Long, rng As Range, revenge As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual
sheetlist = Array("Jackson", "Michael", "Bieber", "Nicole", "Reina", "TimC")
strsearch = Array("Jackson", "Michael", "Bieber", "Nicole", "Reina", "TimC")
Set rng = Range("B5:S2641")
Set revenge = Range("A4:Z4")

For Z = LBound(strsearch) To UBound(strsearch)
For i = LBound(sheetlist) To UBound(sheetlist)
        Sheets("Sheet1").Activate
        lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        raio = "Revsev"
        
        For x = lastrow To 5 Step -1
                If ActiveSheet.Cells(x, 2) = strsearch(Z) _
                    And ActiveSheet.Cells(x, 11) <= 0.555 Then
                        Rows(x).Copy Sheets(sheetlist(i)).Cells(Rows.Count, 1).End(xlUp)(2)
                    End If
                Next x
            Next i
        Next Z
            
        
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

so essentially it would be start out as for z = blah to blah and i= blah to blah and it would end with next i and z I don't know if that's possible or if theres another way around this. Thanks.

 

[WarPigl3t]

First I'd like to mention a possible error in your code.

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

I don't think you can use Letter references in the Cells method. You have to modify this to...

lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlup).Row

 

[WarPigl3t]

As for the rest of your code. I'm having trouble understanding it. It's always simpler if you just post a dataset of what it looks like before you run the macro, and a seperate dataset of what it should look like after you run the macro.

 

[Rick Rothstein]

First I'd like to mention a possible error in your code.

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

I don't think you can use Letter references in the Cells method.

The column argument for the Cells property can take either the column number or the column's letter designation. I would note the ActiveSheet reference could be omitted as it is the default when omitted (of course including it does no harm), but aside from that, the code line is fine as written.

 

[pdevito3]

WarPiglet, the column letter designation "A" is definitely a valid argument for Cells().

tahir9, I am also having trouble understanding your code. From what I understand though, there is a possibility to use a "helper" variable. So you would eliminate one of the for loops, and use a helper variable instead. Assign the helper the first value it is needed and, if can be incremented as it seems, you can assign it's new value at the end of your for loop, satisfying both variables requirements. Hope this helped.

 

[WarPigl3t]

The column argument for the Cells property can take either the column number or the column's letter designation. I would note the ActiveSheet reference could be omitted as it is the default when omitted (of course including it does no harm), but aside from that, the code line is fine as written.

Awesome. I didn't know that.

 

[tahir9]

Sorry guys for poor format still a beginner and trying to get better at writing the code. The code in simpler version is saying look for this name in column in 2 then go look at column 7 and see if its less than .555, if its true copy and paste the value to the next sheet, then go the next row and do the same kind of thing. Once it does that for one name, then its supposed to move onto the next name in the array i.e Michael.

So pdevito you're saying instead of having the for loop for the names of the people just have the value z = z+1 after the next x.

And would I label z the same thing it is now instead of without the for in front?