Hello there

I've got a bit of a challange here, and its a two part question. I'll give you the scenario.

I have a requirement to import Item sales into a particular accounting package. A supplier sends a CSV file outlining their order - however it is not in a format the accounting package needs to successfully import.

So, since the incoming file is csv, it made sense to me to use Excel to adjust it into the required format.

The CSV structure is as follows;

A B C D E
Barcode# ItemDescription Branch Qty Cost
999-991 PK WScrews (20) 112 10 910
999-912 PK Gal.Nails(40) 181 25 880
999-943 CLOTH blue 162 26 870
999-914 PK MASl.Nails(40) 181 5 830
999-978 TBE S.Glue 112 12 720

The aim is to modify this base structure to a form that meets the import conditions of the accounting software. These conditions are

-Decimalised cost, and cost+tax must be present
-Separate invoices with blank rows between items

I have achieved each condition with the following macros

-Decimalised cost, and cost+tax must be present

These guys identify $0.72 as 720, and so I need to divide each cost item by 1000 to make it fit with the accounting software requirements...

Sub Divide_J_By_1000()
'
' Divide_J_By_1000 Macro
' Macro recorded 12/04/2005
'

'
ActiveWindow.LargeScroll Down:=2
ActiveWindow.SmallScroll ToRight:=15
ActiveWindow.SmallScroll Down:=48
ActiveWindow.SmallScroll ToRight:=28
Range("BM221").Select
ActiveCell.FormulaR1C1 = "1000"
Range("BM221").Select
Selection.Copy
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
:=False, Transpose:=False
End Sub

I know its not efficient because of the scrolling - but speed isnt an issue, the most lines this extract will contain is 200 rows....I wanted to hide the copy of 1000 from a cell - I'm not really up on VBA stuff...

In order to add the tax inclusive amount I used the following macro

Sub Add_GSTinc_in_N()
'
' Add_GSTinc_in_N Macro
' Macro recorded 12/04/2005
'

'
Range("N1").Select
ActiveCell.FormulaR1C1 = "=RC[-4]+((RC[-4]/100)*12.5)"
Range("N1:N346").Select
ActiveWindow.SmallScroll Down:=-111
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 1
Columns("N:N").Select
Selection.FillDown
End Sub

Which works well aside from the fact that it fills down for infinity....

-Separate invoices with blank rows between items

Sub Insert_Columns()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Cells(i, 1).EntireRow.Insert
Next
End Sub

Which is fine, but offers not facility to group items based on thier branch number. This means that each item will appear on a separate invoice - not much use, but a step there.



SO! If you have made it this far Here is what I would like help with.

Primarily

1. How would I go about sorting the data based on the branch ID? This would effectively group all sales into invoices for each branch.

2. Once sorted, I would need to separate each branch group with a blank row. This is to tell the accounting software to move on to a new invoice.

Secondarily

1. How can I stop a fill down process at the end of the data? Without a mechanism to stop it, when I fill down the price including tax column, it will carry on beyond the data. Since each month the data extract will be different (be longer, shorter) I cant reference hard x,y points. I need a dynamic way to establish the data has ended, and so the fill should end there also. This problem also effects the fill-down I use to achieve the cost+tax figure.

2. What would be the best way to merge all these macros together to achieve all tasks in one go?

If you can help with any side to this problem, then your thoughts would be most appreciated. Even guessing is helping me, as I will continue to try and nut it out on my own - ideas from the crowd could be just as helpful as a full on solution.

Thanks in advance

~Keef