VBA Loop question

[vanik16]

Hi All,

New to VBA, but I have some VBA code to call a sub which copies, tranposes and pastes the row as a column in a different sheet.

It loops through rows 1 to 10.

The bit I'm stuck on is the syntax for .Range("B:Z") to include the X, tried things like .Range("B:Z" & X) or .Range("B" & X : "Z" & X) which don't work.

.Range("A" & X) works fine. Could anyone help with the correct syntax for this?

For X = 1 To 10

Call transposeAndPasteRow(Worksheets("Sheet 1").Range("A" & X), Worksheets("Sheet 2").Range("A999999").End(xlUp))

Call transposeAndPasteRow(Worksheets("Sheet 1").Range("B1:Z1"), Worksheets("Sheet 2").Range("C999999").End(xlUp))

Next X

Thanks for the help in advance!

 

[6StringJazzer]

.Range("B" & X : "Z" & X) should be valid but I don't really know what you are trying to do. No idea what transposeAndPasteRow does or what it expects as an argument.

What do you mean "don't work"? What are you expecting, and what happens instead?

 

[vanik16]

Thanks for the reply,

"transposeAndPasteRow" sub just copies a row and transposes to a column as below.

I'm trying to loop through all the rows in Sheet 1, and transpose them to Sheet 2.

For the line Call transposeAndPasteRow(Worksheets("Sheet 1").Range("A" & X), Worksheets("Sheet 2").Range("A999999").End(xlUp)) it copies the cell A1 to A10 and transposes it into the same number of rows. This works as intended.

For Call transposeAndPasteRow(Worksheets("Sheet 1").Range("B1:Z1"), Worksheets("Sheet 2").Range("C999999").End(xlUp)), I'm trying to copy the entire row B1 to Z1, looping through each row to B10 to Z10. If I enter the syntax .Range("B" & X : "Z" & X) , it comes up with a Compile error: Expected list seperator or )

Sub transposeAndPasteRow(rowToCopy As Range, pasteTarget As Range)

    pasteTarget.Resize(rowToCopy.Columns.Count) = Application.WorksheetFunction.Transpose(rowToCopy.Value)

End Sub

 

[vanik16]

Ah nevermind, I solved it.

I was missing a , instead of the : for .Range("B" & X , "Z" & X)

 

[Joe4]

I was missing a , instead of the : for .Range("B" & X , "Z" & X)

Note that the difference is if you use the ":", it will include ALL columns from column B to column Z.
If you use the ",", it will ONLY be columns B and Z (and not any of the columns in between).

 

[6StringJazzer]

Sorry, syntax error. Use this

.Range("B" & X & ":Z" & X)