Aaron71
06-24-2010, 01:13 PM
Hello,
I am very new to Excel VBA. I am a winemaker and this application is to help with ordering bottles. I am working on a spreadsheet that takes the values (the number of pallets of a type of glass) of my glass needs and distributes them in order of need on trucks and fills them to 26 pallets (full load). It continues down the rows looking at each type of glass.
Example:
Glass A – 30 pallets
Glass B – 6 pallets
Glass C – 5 pallets
Glass D – 17 pallets
Result:
Truck 1 – 26 pallets of “A” (Full load)
Truck 2 – 4 pallets of “A”, 6 pallets of “B”, 5 pallets of “C”, and 11 pallets of “D” (Full load)
Truck 3 – 6 pallets of “D”, room for more glass.
I am starting the ability to scan down the rows, and insert a line after glass with full trucks. Right now I'm having trouble with how to select a range of columns (i.e. “B4:C5”, so , and on down the list) so that I can use the "Fill Down" function. The reason is to get the first row with GlassA as 26 pallets and then to place the other 4 pallets of GlassA needed in the next Row. I have looked at numerous sites for examples and havn't found any that I understand. Is this something that you would be willing to give me some help on?
I need something like this:
Range(i,1 to (i+1),4).Select in order to select a variable range.
Here's what I have so far.
Sub Macro1()
'Test Macro for Development
Dim i As Integer
For i = 2 To 20
If cells(i, 2) > 26 Then
cells(i, 3) = "26"
cells((i + 1), 1).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Else: cells(i, 3) = cells(i, 2)
End If
Next i
End Sub
I am very new to Excel VBA. I am a winemaker and this application is to help with ordering bottles. I am working on a spreadsheet that takes the values (the number of pallets of a type of glass) of my glass needs and distributes them in order of need on trucks and fills them to 26 pallets (full load). It continues down the rows looking at each type of glass.
Example:
Glass A – 30 pallets
Glass B – 6 pallets
Glass C – 5 pallets
Glass D – 17 pallets
Result:
Truck 1 – 26 pallets of “A” (Full load)
Truck 2 – 4 pallets of “A”, 6 pallets of “B”, 5 pallets of “C”, and 11 pallets of “D” (Full load)
Truck 3 – 6 pallets of “D”, room for more glass.
I am starting the ability to scan down the rows, and insert a line after glass with full trucks. Right now I'm having trouble with how to select a range of columns (i.e. “B4:C5”, so , and on down the list) so that I can use the "Fill Down" function. The reason is to get the first row with GlassA as 26 pallets and then to place the other 4 pallets of GlassA needed in the next Row. I have looked at numerous sites for examples and havn't found any that I understand. Is this something that you would be willing to give me some help on?
I need something like this:
Range(i,1 to (i+1),4).Select in order to select a variable range.
Here's what I have so far.
Sub Macro1()
'Test Macro for Development
Dim i As Integer
For i = 2 To 20
If cells(i, 2) > 26 Then
cells(i, 3) = "26"
cells((i + 1), 1).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Else: cells(i, 3) = cells(i, 2)
End If
Next i
End Sub