VBA

Forumthread: 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

AW: VBA

10.11.2020 08:34:24

peterk
AW: VBA

10.11.2020 08:42:49

MRUTOR
AW: VBA

11.11.2020 18:21:33

Martien
AW: VBA

16.11.2020 21:20:28

Piet

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

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

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

Beliebteste Forumthreads (12 Monate)

Forumthread: VBA

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

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread