Code to check cell for execution to carry out below in a worksheet

[dwheggen1]

I am building a daily order form that pulls data into workbook named "Daily Order"

Order # 1 data is pulled from list generated from a user form, The qty. of Trucks needed (could be from 1 to 15) and change with every order
I wish to enter a row for each truck needed (1 needed = 1 Row, 15 needed = 15 rows)
Next to the number ( 1-15) it would also list truck type needed

Suggested layout would be:

Column A = Blank outline box
Column B = Number (1-15)
Column C = Truck Type
Column D = Truck dispatched would be selected from a drop Down List depending on truck type needed (Lists are on workbook "Truck Type")
If a specific truck is selected it would then be removed from the available list for that day.

After order # 1 is filled, I would then pull in order # 2, and so on.

I hope I have been clear as to what process I am looking the codes to perform.

Thanks in advance for all of help provided

Dennis

[ARowbot]

Created a button called process orders on your spreadsheet. Trucking orders.xlsm

This hopefully currently does what you want for order number 1?

Can you confirm how you would like order 2 etc to be treated..
Could do this by just manually filling one in completely...

Arowbot

[dwheggen1]

AROBOT, Great

Can you help me with drop down selection list for column "D"

If stockpile truck is in Pulled into Column "C" the dropdown list would look to workbook
"Truck Companies" and use the appropriate list as needed. After A truck is used it would no longer be available for selection for that day.

Order # 2 will then need to be generated below needed space for order # 1 and so on

I would think by selecting the button you added it would look at the next order # until eventually all orders are filled

Thanks
Dennis

[ARowbot]

Hi,
I'm unsure about the drop down list. However I have created the structure for you to use for all orders.

Please Login or Register  to view this content.

[dwheggen1]

Thanks, Cool works well.

I have now entered area for Order # 2 Data to be brought in so I need your code to start entering Order # 2 on Row 42 Column B , So it would be 6 rows below the first order if 15 rows are entered

Sub Button6_Click()
Dim n As Integer, i As Integer, t As String, Lastrow As Integer, o As Integer

Lastrow = 0

For o = 1 To 10
n = Range("G12").Offset(-o, 0).Value
t = Range("H12").Offset(-o, 0).Value
For i = 1 To n
Range("B21").Offset(i + Lastrow, 0).Value = i
Range("C21").Offset(i + Lastrow, 0).Value = t

Next i
Lastrow = Lastrow + n
Next o

End Sub

Regards
Dennis

[ARowbot]

If you want to make order 2 start on cell B42 (15+6) rows after order 1 start then you can just change:

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[dwheggen1]

Worked great ....

Now order # 3 would be 6 rows after that ...

Do I deed to change or add more code

Dennis

[ARowbot]

Assuming you are expecting 15 trucks per order then it should be in the correct place for orders 1-10,
If you wish for more orders then just change

Please Login or Register  to view this content.

appropriately.

[dwheggen1]

ARobot.. That works fantastic.

Question though ???

If order # 1 say only needs 1 truck, will the macro only enter the 1 and advance accordingly for others ?

[ARowbot]

It did that originally ...I changed it when you wanted it to start at a specific cell.. If you upload the template again with the format you would like I can tailor it for you.

[dwheggen1]

Here you go

Copy of Trucking Orders 3-28-2015.xlsm

I hope you understand that any order could be from 1 to 15 trucks. If only 1 is needed I would like the form to only space out what it takes for 1, if 5 needed space for 5 and so on.

[ARowbot]

I have updated the code: Trucking orders.xlsm

It now inserts the correct amount of rows in between each of the order headers and then moves onto the next order.
I have spaced it with 4 cells in between each border header. If you adjust this, then you will also have to slightly adjust the code where it says

Please Login or Register  to view this content.

as required.

So if you want to start with 3 cells inbetween the headers, then change the 7 to a 6.

[dwheggen1]

ARobot...

Works fantastic . Does just what I wanted. Now on to new codes to improve program more

Thanks much,
Dennis