macro which takes 100 entries in a column and turns them into a 10x10 matrix

[intothewild]

i need to write a macro which takes 100 entries in column in an excel spreadsheet and turns it into a 10x10 matrix on either the same or another sheet.

in vba code:

How do i read the first 10 entries in a column then cut and paste them into another column,
then take the next 10 down of the 90 that are left and put them in the column to the right of
the first 10 i took away.

Then what happens when theres 110 in the column and i need to have a 11x10 matrix?

cheers

[Andrew-R]

The following code will take any number of cells in column A of the active sheet and use them to make a matrix 10 rows high on the named sheet.

Sub MakeGrid()

Const sTARGET_SHEET = "Sheet2"
Const lCOL_LENGTH = 10
Const lFIRST_ROW = 1
Const lSOURCE_COL = 1
Const lTARGET_ROW=1

Dim lLastRow As Long
Dim lLoop As Long

Sheets(sTARGET_SHEET).Cells.Clear

With ActiveSheet
  lLastRow = .Cells(.Rows.Count, lSOURCE_COL).End(xlUp).Row

  For lLoop = 1 To WorksheetFunction.RoundUp((lLastRow - lFIRST_ROW) / lCOL_LENGTH, 0)
    .Range(.Cells((lLoop - 1) * lCOL_LENGTH + lFIRST_ROW, lSOURCE_COL), .Cells(lLoop * lCOL_LENGTH + lFIRST_ROW - 1, lSOURCE_COL)).Copy Destination:=Sheets(sTARGET_SHEET).Cells(lTARGET_ROW, lLoop)
  Next lLoop
  
End With

End Sub

[intothewild]

cheers Andrew-R

With ActiveSheet
  lLastRow = .Cells(.Rows.Count, lSOURCE_COL).End(xlUp).Row

  For lLoop = 1 To WorksheetFunction.RoundUp((lLastRow - lFIRST_ROW) / lCOL_LENGTH, 0)
    .Range(.Cells((lLoop - 1) * lCOL_LENGTH + lFIRST_ROW, lSOURCE_COL), .Cells(lLoop * lCOL_LENGTH + lFIRST_ROW - 1, lSOURCE_COL)).Copy Destination:=Sheets(sTARGET_SHEET).Cells(lTARGET_ROW, lLoop)
  Next lLoop
  
End With

....can you explain a little how the 'With' section works im not so sure i follow it..

[Andrew-R]

It just tells Excel which object you're working with, once you've made a with statement any properties or methods belonging to the object can be referred to by proceeding them with a period.

So, for example:

With Sheets(1).Range("A1")
  .Value=10
  .Interior.Color=vbRed
End With

Does the same as:

Sheets(1).Range("A1").Value=10
Sheets(1).Range("A1").Interior.Color=vbRed

As I'm working with the active sheet the with statement probably wasn't needed, it was just force of habit.