Dear Experts

I have the following code which I got from running a macro:

Code
-------------------
Range("F212:G454").Select
Selection.Cut
Selection.Cut Destination:=Range("H212:I454")
Range("H212:I454").Select
Range("F456:G699").Select
Selection.Cut Destination:=Range("J456:K699")
Range("J456:K699").Select
Range("F701:G1150").Select
Selection.Cut Destination:=Range("L701:M1150")
Range("L701:M1150").Select
Range("L862:M1150").Select
Selection.Cut Destination:=Range("N862:O1150")
Range("N862:O1150").Select
Range("F1152:G1637").Select
Selection.Cut Destination:=Range("P1154:Q1639")
Range("P1154:Q1639").Select
Range("P1154:Q1639").Select
Range("Q1639").Activate
Selection.Cut Destination:=Range("P1152:Q1637")
Range("P1152:Q1637").Select
Range("P1387:Q1637").Select
Selection.Cut Destination:=Range("R1387:S1637")
Range("R1381").Select

-------------------

As you can see this is very tedious. What I am trying to do i
demonstrated in the following table. The first table is the result
get. Then when I format it with the above code I get the second tabl
which is what I want.


Code
-------------------

Product Category invoice_no qty value
Apple Fruit 127721 2 630
Apple Fruit 126677 2 889.2
Mango Fruit 126980 2 889.2

Cabbage Vegetable 126854 1 421.6
Apple Fruit 126743 1 615
Carrot Vegetable 126673 2 1240
Tomatoe Vegetable 126847 2 2480
Potato Vegetable 127325 1 1240
Apple Fruit 126673 2 1361.52

Carrot Vegetable 418121 4 1260
Potato Vegetable 416401 1 529.2

Carrot Vegetable 415852 3 1845


To this result:

January February March April
Product Category invoice_no qty value qty value qty value qty value
Apple Fruit 127721 2 630
Apple Fruit 126677 2 889.2
Apple Fruit 126743 1 615
Apple Fruit 126673 2 1361.52
Cabbage Vegetable 126854 1 421.6
Carrot Vegetable 126673 2 1240
Carrot Vegetable 41812 4 1260
Carrot Vegetable 41585 3 1845
Mango Fruit 126980 2 889.2
Potato Vegetable 127325 1 1240
Potato Vegetable 41640 1 529.2
Tomatoe Vegetable 126847 2 2480
-------------------

What I have done is to include a space to signify the end of each mont
in the first table. So now when I do my formating, I want the code t
say:

Find the row that is empty, add one to the variable holding this value
Then start selecting the qty and value of that month till you find a
empty space which indicates the end of that month. The qty and valu
must then be moved two spaces to the right (february). Then add one t
the new blank row. Start selecting qty and value for march till yo
reach empty row and move the values four spaces to the right and s
forth and so forth.
eg. This is the code I am trying to adjust to do what the macro i
doing.

Code
-------------------
'Look for the blank row in the sheet
For i = 1 To 1
For j = 2 To lastrow Step 1
With Cells(j, i)
If .Value = "" Then
Cells(j + 1 ,i)
??? Rows(j).Select 'Keep selecting till you reach a blank row
????? 'Move n spaces to right depending on the month
End If
End With
Next
Nex
-------------------


I would really appreciate it if you could help me, especially when yo
you have to muddle thru this long query of mine.

Thanx

kind Regard

--
Message posted from http://www.ExcelForum.com