Without properly reading and understanding what you are
asking it seems that the offset function may be able to
help you.

Look it up in help


-----Original Message-----
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 is
demonstrated in the following table. The first table is
the result I
get. Then when I format it with the above code I get the
second table
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 month
in the first table. So now when I do my formating, I want
the code to
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 an
empty space which indicates the end of that month. The
qty and value
must then be moved two spaces to the right (february).
Then add one to
the new blank row. Start selecting qty and value for
march till you
reach empty row and move the values four spaces to the
right and so
forth and so forth.
eg. This is the code I am trying to adjust to do what the
macro is
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
Next
--------------------


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

Thanx

kind Regards


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

.