Good afternoon,
I have the following macro that changes single lines of data into actuals (so 5 pallets for store A becomes 5 lines of 1 pallet for store A):
My data set has changed so I have adjusted the macro to calculate the additional column's, however it is duplicating the lines based on the wrong column data (it currently duplicates the lines based on priority and I want it to look at pallets).
This is my data the macro uses (A:E is my data the macro produces G:K):
At the moment it is looking in col E to duplicate the lines, how do I make it look in col C?
As I had help creating this macro I am unable to change it correctly, as previously stated I managed to get it too accept the new column but in doing this is has moved my data and resulted in this error.
Any help would be greatly appreciated, I am sure I need to stop it looking a col 5 and change to col 3 but when I change it I get an error and it wont run.
Thanks
Sara
I have the following macro that changes single lines of data into actuals (so 5 pallets for store A becomes 5 lines of 1 pallet for store A):
VBA Code:
Sub Singles()
'
' Singles Macro
'
'Sub aTest()
'Assumes data in columns A:D; headers in row 1
'Results in columns F:I
Dim vData As Variant, vResult As Variant
Dim lNumRows As Long, i As Long, j As Long, lLin As Long
vData = Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row)
lNumRows = Application.Sum(Application.Index(vData, 0, 5))
vResult = Range("g2").Resize(lNumRows, 5)
For i = 1 To UBound(vData, 1)
For j = 1 To vData(i, 5)
lLin = lLin + 1
vResult(lLin, 1) = vData(i, 1)
vResult(lLin, 2) = vData(i, 2)
vResult(lLin, 3) = 1
vResult(lLin, 4) = vData(i, 4)
vResult(lLin, 5) = vData(i, 5)
Next j
Next i
Range("g1:k1").Value = Range("A1:e1").Value
Range("g2").Resize(lNumRows, 5) = vResult
Columns("g:k").AutoFit
End Sub
My data set has changed so I have adjusted the macro to calculate the additional column's, however it is duplicating the lines based on the wrong column data (it currently duplicates the lines based on priority and I want it to look at pallets).
This is my data the macro uses (A:E is my data the macro produces G:K):
Store Number | Store Name | Pallets | Split | Priority | Store Number | Store Name | Pallets | Split | Priority | |
114 | Jersey | 9 | DTS South | 1 | 114 | Jersey | 1 | DTS South | 1 | |
780 | Maidstone | 9 | DTS South | 6 | 780 | Maidstone | 1 | DTS South | 6 | |
113 | Chichester | 5 | DTS South | 12 | 780 | Maidstone | 1 | DTS South | 6 | |
139 | Gateshead | 6 | DTS South | 16 | 780 | Maidstone | 1 | DTS South | 6 | |
540 | Stockton | 13 | DTS South | 22 | 780 | Maidstone | 1 | DTS South | 6 | |
635 | Banbury | 8 | DTS South | 25 | 780 | Maidstone | 1 | DTS South | 6 | |
595 | Oxford | 7 | DTS South | 26 | 780 | Maidstone | 1 | DTS South | 6 | |
133 | Crawley | 1 | DTS South | 28 | 113 | Chichester | 1 | DTS South | 12 | |
65 | Horsham | 7 | DTS South | 29 | 113 | Chichester | 1 | DTS South | 12 | |
116 | Farnborough | 6 | DTS South | 30 | 113 | Chichester | 1 | DTS South | 12 | |
870 | Reading | 11 | DTS South | 32 | 113 | Chichester | 1 | DTS South | 12 | |
At the moment it is looking in col E to duplicate the lines, how do I make it look in col C?
As I had help creating this macro I am unable to change it correctly, as previously stated I managed to get it too accept the new column but in doing this is has moved my data and resulted in this error.
Any help would be greatly appreciated, I am sure I need to stop it looking a col 5 and change to col 3 but when I change it I get an error and it wont run.
Thanks
Sara
Last edited by a moderator: