Slight change required to Macro

Batley

New Member
Joined
Jun 8, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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):

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 NumberStore NamePalletsSplitPriorityStore NumberStore NamePalletsSplitPriority
114Jersey9DTS South1114Jersey1DTS South1
780Maidstone9DTS South6780Maidstone1DTS South6
113Chichester5DTS South12780Maidstone1DTS South6
139Gateshead6DTS South16780Maidstone1DTS South6
540Stockton13DTS South22780Maidstone1DTS South6
635Banbury8DTS South25780Maidstone1DTS South6
595Oxford7DTS South26780Maidstone1DTS South6
133Crawley1DTS South28113Chichester1DTS South12
65Horsham7DTS South29113Chichester1DTS South12
116Farnborough6DTS South30113Chichester1DTS South12
870Reading11DTS South32113Chichester1DTS South12


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:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
How about
VBA Code:
Sub Singles()
'
' Singles Macro
'
    '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))
    ReDim vResult(1 To lNumRows, 1 To 5)
      
    For i = 1 To UBound(vData, 1)
        For j = 1 To vData(i, 3)
            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
 
Upvote 0
Solution
Hi
Try
VBA Code:
Option Explicit

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, 3))
    vResult = Range("g2").Resize(lNumRows, 5)

    For i = 1 To UBound(vData, 1)
        For j = 1 To vData(i, 3)
            lLin = lLin + 1
            vResult(lLin, 1) = vData(i, 1)
            vResult(lLin, 2) = vData(i, 2)
            vResult(lLin, 3) = vData(i, 3)
            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
 
Upvote 0
Thank you that works perfectly!

So just to clarify it is just one line that you changed:
For j = 1 To vData(i, 5) Original
For j = 1 To vData(i, 3) New

This is the line that tells it which column to look at (not col 5 but col 3)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top