CODEWITHSUNDEEP
vbaexcelfor-looprange
Tony
Tony

Reputation: 394

For loop with multi variable

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!

Upvotes: 1

Views: 289

Answers (2)

Tony
Tony

Reputation: 394

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.

Upvotes: 0

Tylor Hess
Tylor Hess

Reputation: 679

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

Upvotes: 1

Related Questions