PDA

View Full Version : Help with Variables in Ranges



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

Bob Phillips
06-24-2010, 01:51 PM
I c an see that you want to insert a row below row 5, but what do you want to put in there?

mdmackillop
06-24-2010, 02:29 PM
Can you post an "after" result.

Aaron71
06-25-2010, 06:38 AM
I am sending another attachment that will hopefully be more clear. I really appreciate any help I can get.:dunno