What's wrong with this loop in my code?

[5inco]

Hi!

I have this code:

---

Sub Copy()

Dim Row As Long, Col As Long

Set Dest = Sheets(3).Range("B" & Rows.Count).End(xlUp).Offset(1)
Sheets(1).Activate
Dim v As Variant
Z = Array("E", "D", "F")

For Row = 17 To Range("A" & Rows.Count).End(xlUp)
For Col = 0 To UBound(Z)
If Sheets(1).Cells(Row, Z(Col)) <> "" Then
Sheets(1).Cells(Row, Z(Col)).Copy Dest
Next Col
End If
Next Row


End Sub

---

It doesn't work. It doesn't do the loop. What am I doing wrong?

---

What I want the macro to do is this:

1. Start on row 17 Sheet1 and evaluate columns D, E, F
2. If cell on column E has code, it should copy it to first empty cell in column B on sheet2. If not, it should see if column D has code and do the same. If not, then it should go to cell in column F.
3. Do the same on each row with data on sheet1

I've managed to do some steps, but the loops don't work

Any help?

 

[Joe4]

Welcome to the Board!

One thing that stands out at first glance is that this:

For Row = 17 To Range("A" & Rows.Count).End(xlUp)

should be this:

For Row = 17 To Range("A" & Rows.Count).End(xlUp).Row

I haven't analyzed all your code yet, so I don't know if there may be other problems too, but that should give you a good start.

By the way, a good way to debug these type of issues is to resize your VB Editor window to about a quarter size of the screen so you can see both that and the Excel sheet at the same time.
Then, use F8 to step through your code line-by-line, and watch what it is doing. If you hover your cursor over variable names in your VBA code, it will show you their value at that time.

Armed with this new knowledge, why don't you try to make the change I suggested and if your code still isn't working, trying stepping through your code to see if you can locate where the issue is.

 

[mumps]

This may do it.

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Sheets("Sheet1").Range("E2:E" & LastRow)
        If rng <> "" Then
            rng.Copy Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        ElseIf rng = "" Then
            If rng.Offset(0, -1) <> "" Then
                rng.Offset(0, -1).Copy Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            ElseIf rng.Offset(0, -1) = "" Then
                rng.Offset(0, 1).Copy Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Jubjab]

Is that really your code? Because it has a syntax error in it:

For Col = 0 To UBound(Z)
If Sheets(1).Cells(Row, Z(Col)) <> "" Then
Sheets(1).Cells(Row, Z(Col)).Copy Dest
Next Col
End If

The Next col and End if should be the other way around.

 

[bRitch022]

Also, with:

Set Dest = Sheets(3).Range("B" & Rows.Count).End(xlUp).Offset(1)
Sheets(1).Activate

Try changing it to:

Set Dest = Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1)
Sheet1.Activate

Or:

Set Dest = Sheets("Sheet Name").Range("B" & Rows.Count).End(xlUp).Offset(1)
Sheets("Sheet Name").Activate

I'm pretty new at VBA, but it's worth a shot?

 

[5inco]

Thanks for your help. I know about Ctrl + F8, not about the variables, but your advice didn't work. Sorry.

 

[5inco]

Mumps,This one almost does it, but the thing is the macro has to evaluate first column D, then if it's blank, E and then F. Yours copies all the codes, because of the way it finds text on the first step. I need to evaluate each row in that column order.

Thanks for your time

 

[5inco]

Jubjab, I changed what you've said, but it only does one loop. I don't know why. Thanks for your time.

Is that really your code? Because it has a syntax error in it:

For Col = 0 To UBound(Z)
If Sheets(1).Cells(Row, Z(Col)) <> "" Then
Sheets(1).Cells(Row, Z(Col)).Copy Dest
Next Col
End If

The Next col and End if should be the other way around.

 

[5inco]

Not working, but thank you so much for your good will!!

Also, with:

Set Dest = Sheets(3).Range("B" & Rows.Count).End(xlUp).Offset(1)
Sheets(1).Activate

Try changing it to:

Set Dest = Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1)
Sheet1.Activate

Or:

Set Dest = Sheets("Sheet Name").Range("B" & Rows.Count).End(xlUp).Offset(1)
Sheets("Sheet Name").Activate

I'm pretty new at VBA, but it's worth a shot?

 

[5inco]

So maybe the thing is, How can I do this on each column starting on column 17 and evaluating only columns F,E and G in that order

Sub test()


Set Org = Sheets(1)
Set Dst = Sheets(3).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
If Org.Range("F17") <> "" Then
Org.Range("F17").Copy Dst
ElseIf Org.Range("E17") <> "" Then
Org.Range("E17").Copy Dst
Else
Org.Range("G17").Copy Dst
End If
End Sub