For nested parallel VBA Excel

Asked

Viewed 600 times

0

I’m performing a For inside another For, where you run by column, find the cell and copy in the other For run column as well and paste into a range. However, the code is performing an entire For first to then perform the other and I need you to execute both (copying and pasting).

Can you help me? Below the code I performed...

SUB ()

Set Cop = Workbooks(Arq)
L = Cop.Sheets("Consolidado_Vendas_TFN").Range("J1048576").End(xlUp).Row

   For w = 15 To 135

        If Cells(6, w) <> "" Then
           Cells(6, w).Copy

        End If

            For y = 18 To 138

                If Cells(8, y) = "" Then
                   Sheets("Consolidado_Vendas_TFN").Range(Cells(8, y), Cells(L, y)).PasteSpecial xlValues

                   w = w + 4
                   y = y + 4

                End If


        If w = 136 And y = 139 Then

            Exit For
    Exit For

            Else
                w = w - 1
                y = y - 1

        End If

        Next y
    Next w

END SUB
excel-vba

  • What do you really want? Could you put pictures? Because your code shows that you copy a cell from a non-empty column and try to paste into a range from row 8 to the last row of column J

2 answers

0

A for loop

No need Using copy/Paste is slower than simply writing, 100 lines is feasible. But for more than 20000 lines can become slow.

Worksheet actions can be achieved only with a for loop, without the need to nested for loops.

Sub teste()

Set Cop = Workbooks(Arq)
L = Cop.Sheets("Consolidado_Vendas_TFN").Range("J1048576").End(xlUp).Row

   For w = 15 To 135 Step 4
        y = w + 3
        If Cells(6, w) <> "" And Cells(8, y)="" Then
           Sheets("Consolidado_Vendas_TFN").Range(Cells(8, y), Cells(L, y)) = Cells(6, w)
        End If

        If w = 136 And y = 139 Then
            Exit For
            Else
                w = w - 1
                y = y - 1
        End If
    Next w

End Sub
  • w = w + 4 can be replaced by Step 4
  • Since the relation of w to y is y=w+3, the other for can be removed
  • If may contain more than one condition, using operators such as E(And).

Note: I don’t understand what this part is for:

    If w = 136 And y = 139 Then
        Exit For
        Else
            w = w - 1
            y = y - 1
    End If

Using Copy/Paste

Set Cop = Workbooks(Arq)

   For w = 15 To 135 Step 4
    ultimaLinha = Cop.Sheets("Consolidado_Vendas_TFN").Cells(Cop.Sheets("Consolidado_Vendas_TFN").Rows.Count, w).End(xlUp).Row
        y = w + 3
        If Cells(6, w) <> "" And Cells(8, y) = "" Then
            Sheets("Consolidado_Vendas_TFN").Cells(6, w).Copy
            Sheets("Consolidado_Vendas_TFN").Range(Cells(8, y), Cells(L, y)).PasteSpecial xlValues
        End If
    Next w

0

Daniel, It worked and I really appreciate the help!!! I just made a small change because I really need to copy from a cell and paste into a range.

Just this part I needed to change...

If Cells(6, w) <> "" And Cells(8, y) = "" Then
   Sheets("Consolidado_Vendas_TFN").Cells(6, w).Copy
   Sheets("Consolidado_Vendas_TFN").Range(Cells(8, y), Cells(L, y)).PasteSpecial xlValues
End If

Browser other questions tagged excel-vba

You are not signed in. Login or sign up in order to post.