Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1792to1796
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA

VBA
10.11.2020 07:40:28
Martien
Hello,
I`m working on a file where I want to loop through all rows, step by step. I have a file with machine info like brandname, article numbers etc.
The difficulty is that in the row I have to ranges where I have to do something. Due to the fact that e.g. column AH till AL holds data which I want to copy to another workbook and Paste special with Transpose. This is something I know how to do for one row.
After this copy and paste step I want to move one row down to do the same. I can do this now by changing the Row number from Range("AH2:AL2") to next Range("AH3:AL3") manual.
Is there a function where I can loop to another row?
Thanks

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
  • 10.11.2020 08:34:24
    peterk
  • 10.11.2020 08:42:49
    MRUTOR
  • 11.11.2020 18:21:33
    Martien
  • 16.11.2020 21:20:28
    Piet
Anzeige
AW: VBA
10.11.2020 08:34:24
peterk
Hello
You can use OFFSET , example do the job for "AH2:AL2" to "AH102:AL102"

For i = 0 to 100
Range("AH2:AL2").offset(i).copy
'paste
next i

AW: VBA
10.11.2020 08:42:49
MRUTOR
Hello Martien,
yes you can do that with a For loop. This loop you can tell where to start and where to finish. You need to declare a long variable as counter for the loop. Then in the opening of the loop you define the start and end point. E.g for your example I start in row 2 and finish in row 5. In my example code I copy the range from sheet 1 and paste the values into column A in same row number on sheet 2. And this from row 2 until row 5:

Sub test()
Dim a As Long
For a = 2 To 5
Sheets("Sheet1").Range("AH" & a & ":AL" & a).Copy
Sheets("Sheet2").Range("A" & a).PasteSpecial xlPasteValues
Next a
End Sub

Hope it helps.
Greetings Tor
Anzeige
AW: VBA
11.11.2020 18:21:33
Martien
Hi Tor,
The code you have prepares is working thanks for that I have an additional question. Is it possible to run a Do Loop within the code you have with For - Next loop
With F8 it steps over the Do Loop.
Sub test()
Dim x As Long
Dim i As Integer
For x = 2 To 5
Sheets("Startlijst").Select
Range("AH" & x & ":HP" & x).Copy
Sheets("Uitwerking").Select
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
i = 1
Do While Cells(i, 1).Value  ""
Sheets("Startlijst").Select
Range("A3:HP3").Select
Selection.Copy
Sheets("Uitwerking").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Paste:=True
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
Next x
End Sub

Anzeige
AW: VBA
16.11.2020 21:20:28
Piet
Hallo
Sorry, my English is not good. But you write a Program without "Select" the Sheets! Look Down ...
I hope you understand this small Code. I hope it is correkt.
mfg Piet
Sub test()
Dim x As Long
Dim i As Long     'Integer = max 30000 Zeilen!!
Dim lz As Long    'LastZell Variable  (xlUp)
For x = 2 To 5
lz = Sheets("Uitwerking").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Startlijst").Range("AH" & x & ":HP" & x).Copy
Sheets("Uitwerking").Cells(lz, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
i = 1
Do While Cells(i, 1).Value  ""
Sheets("Startlijst").Range("A3:HP3").Copy
Sheets("Uitwerking").Cells(i, 1).PasteSpecial Paste:=xlPasteAll, Paste:=True
i = i + 1
Loop
Next x
End Sub

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige