Move ? spaces to the right

[poppy]

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

 

[Nick]

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

 

[Bob Phillips]

Hi Poppy,

Here is a little macro (watch wrap-around)

Option Explicit

Sub For5matStuff()
Dim oWSSource As Worksheet, oWsTarget As Worksheet
Dim iStartRow As Long, iEndRow As Long
Dim iStartCol As Long, iMonthNum As Long
Dim iTargetRow As Long, iTargetStartRow As Long
Dim oRngSort As Range
Dim i As Long

Set oWSSource = Worksheets("Sheet1")
Set oWsTarget = Worksheets("Sheet2")
iStartRow = 1
iStartCol = 1
iTargetStartRow = 1
iTargetRow = iTargetStartRow + 1
iMonthNum = 1

With oWSSource
iEndRow = .Cells(Rows.Count, iStartCol).End(xlUp).Row
oWsTarget.Cells.ClearContents
oWsTarget.Cells(iTargetStartRow, iStartCol).Value = _
.Cells(iStartRow, iStartCol).Value
oWsTarget.Cells(iTargetStartRow, iStartCol + 1).Value = _
.Cells(iStartRow, iStartCol + 1).Value
oWsTarget.Cells(iTargetStartRow, iStartCol + 2).Value = _
.Cells(iStartRow, iStartCol + 2).Value
oWsTarget.Cells(iTargetStartRow, iStartCol + 1 + (iMonthNum *
2)).Value = _
.Cells(iStartRow, iStartCol + 2).Value
oWsTarget.Cells(iTargetStartRow, iStartCol + 1 + (iMonthNum * 2) +
1).Value = _
.Cells(iStartRow, iStartCol + 3).Value
For i = iStartRow + 1 To iEndRow
If .Cells(i, iStartCol).Value = "" Then
iMonthNum = iMonthNum + 1
oWsTarget.Cells(iTargetStartRow, iStartCol + 1 + (iMonthNum
* 2)).Value = _
.Cells(iStartRow, iStartCol + 2).Value
oWsTarget.Cells(iTargetStartRow, iStartCol + 1 + (iMonthNum
* 2) + 1).Value = _
.Cells(iStartRow, iStartCol + 3).Value
Else
oWsTarget.Cells(iTargetRow, iStartCol).Value = _
.Cells(i, iStartCol).Value
oWsTarget.Cells(iTargetRow, iStartCol + 1).Value = _
.Cells(i, iStartCol + 1).Value
oWsTarget.Cells(iTargetRow, iStartCol + 2).Value = _
.Cells(i, iStartCol + 2).Value
oWsTarget.Cells(iTargetRow, iStartCol + 1 + (iMonthNum *
2)).Value = _
.Cells(i, iStartCol + 3).Value
oWsTarget.Cells(iTargetRow, iStartCol + 1 + (iMonthNum * 2)
+ 1).Value = _
.Cells(i, iStartCol + 4).Value
iTargetRow = iTargetRow + 1
End If
Next i
End With

With oWsTarget
Set oRngSort = .Cells(iTargetStartRow, iStartCol).Resize(iEndRow,
(iMonthNum * 2) + 2)
oRngSort.Sort key1:=.Cells(iTargetStartRow + 1, iStartCol), _
header:=xlYes
End With

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[poppy]

Hi Bob

Thanx for that macro. It works fine with sheets that have the exac
number of columns in my example table, but when it enounters anythin
more than that it starts giving problems, but I am working on it. If
come accross any problem I cannot solve I'll get back to you.

Thanx

Kind Regard