For loop with multi variable

Question

I am trying to transform a list of operations to a For loop in Excel VBA.

Sheets("Clients").Select 
Range("A7").Select
Selection.Copy 
Sheets("DB TEMP").Select 
Range("A2:A13").Select
ActiveSheet.Paste
Sheets("Clients").Select
Range("B7,D7,F7,H7,J7,L7,N7,P7,R7,T7,V7,X7").Select
Selection.Copy
Sheets("DB TEMP").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

And imagine that you have to do that for x times I started thinking create a For loop maybe it is as easy as in Python.

Here is my code for now (that is not working)

Dim Cellule As Range
Dim Feuille As Worksheet

Sheets("Clients").Select

For Each Cellule In Range("A1:A260")         'For all cells in the column A

    If Cellule <> "" Then         '#if the cell is not null
        Cellule.Select            '#select the cell (if possible to select the adjacent one too in B
        Selection.Copy            '#copy it 
        Sheets("DB TEMP").Select  '#go to other sheets
        With Sheets("DB TEMP")    '#go find the last row available in column A        
            LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        End With
        ActiveSheet.Paste         '#paste the data   

        Sheets("Clients").Select  '#Back in my main sheets



        Range(Cells(Cellule.ActiveCell.Row, B), Cells(Cellule.ActiveCell.Row, D), Cells(Cellule.ActiveCell.Row, F), Cells(Cellule.ActiveCell.Row, H), Cells(Cellule.ActiveCell.Row, J), Cells(Cellule.ActiveCell.Row, L), Cells(Cellule.ActiveCell.Row, N), Cells(Cellule.ActiveCell.Row, P), Cells(Cellule.ActiveCell.Row, R), Cells(Cellule.ActiveCell.Row, T), Cells(Cellule.ActiveCell.Row, V), Cells(Cellule.ActiveCell.Row, X)).Select



            Selection.Copy
            Sheets("DB TEMP").Select
        With Sheets("DB TEMP")
            LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
        End With
            Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True


    End If

Range(Cells(Cellule.ActiveCell.Row, B) .... this line is supposed to select all the cells with the same row number that is active (Cellule previously)

With Sheets("DB TEMP") ....same stuff as the other one I want to paste it to the other sheets and transpose it from the last row available.

I think I have errors in the Range selection and how to tell excel that my row is the same number.

When I try to run the only first part of the For loop I only get my last none null data to be paste to the other sheets (what about the others even if i see excel check every one of them during the process)

Thank you in advance for any clues on that!

I can see your code doing something multiple times, but what would be changing in each loop? You may want to wrap it into a sub/function and just pass different parameters to it. — user2140173
– user2140173, Commented Dec 5, 2013 at 15:19
You can remove many of your .Selects and Selections. by combining them i.e. Cellule.Copy — Tylor Hess
– Tylor Hess, Commented Dec 5, 2013 at 16:04

Tylor Hess · Accepted Answer · 2013-12-06 12:34:42Z

1

If you want to use Select:

Sheets("DB TEMP").Range("A" & .Rows.Count).End(xlUp).Select
Selection.PasteSpecial

because it looks like you don't actually tell it where to paste.

or if you want to find the LastRow

Sheets("DB TEMP").Cells(LastRow, 1).PasteSpecial

If your problem is cellule.select then try:

selectRow = Cellule.Row
Sheets("NameOfYourSheet").Range("A" & selectRow).Copy

edited Dec 6, 2013 at 12:34

answered Dec 5, 2013 at 17:26

Tylor Hess

6795 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tony Over a year ago

I have an error on my first Cellule.Select (error 1004 the method Select of the class range have failed)

Tony · Accepted Answer · 2013-12-09 17:26:40Z

Private Sub cmdTransferer_Click()

    Dim lgLigFinC As Long
    Dim lgLigFinT As Long
    Dim lgLigFinT2 As Long
    Dim CNV As Range


   'For each cell in the column A
   For Each CNV In Sheets("Clients Test v2").Range("A2:A230")

   'If cell is none empty then select it
    If CNV <> "" Then
       CNV.Select

   'Start a boucle to copy my lines 12 times (I am pretty sure there is an easyer way)
    For i = 1 To 12

    'Last empty line in my Location to paste (column A)
    lgLigFinT = Worksheets("DB Temp V2").Range("A" & Cells.Rows.Count).End(xlUp).Row + 1

    ' Copy my data of the active cell to the first empty line of my paste location
    Worksheets("Clients Test v2").Range("A" & ActiveCell.Row).Copy Destination:=Worksheets("DB Temp V2").Range("A" & lgLigFinT)
    Next


' Copy my column data and transpose it (careful if your cell is empty it will not match the precedent copy paste
lgLigFinT2 = Worksheets("DB Temp V2").Range("F" & Cells.Rows.Count).End(xlUp).Row + 1
    Worksheets("Clients Test v2").Application.Union(Range("E" & ActiveCell.Row), Range("G" & ActiveCell.Row), Range("I" & ActiveCell.Row), Range("K" & ActiveCell.Row), Range("M" & ActiveCell.Row), Range("O" & ActiveCell.Row), Range("Q" & ActiveCell.Row), Range("S" & ActiveCell.Row), Range("U" & ActiveCell.Row), Range("W" & ActiveCell.Row), Range("Y" & ActiveCell.Row), Range("AA" & ActiveCell.Row)).Copy
    Worksheets("DB Temp V2").Range("F" & lgLigFinT2).PasteSpecial Transpose:=True

    End If
Next

  End Sub

I am pretty sure there is an easy way for some elements but it work for me! Thank for your tips I have picked a bit of this and that so I will upvote for Hessr17.

Collectives™ on Stack Overflow

For loop with multi variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related