Loop with column header match and two conditions paste

[Gliffix101]

I don't know how to ask this question so if there is a thread that already addresses this, please point me in that direction because I am completely lost on where to even start.

I have two sheets: Order Form (where customers enter order data) and Order (database view in the back end)

I have a form in which users enter their Store Name (Order Form - Cell C3), Today's Date (Order Form - Cell H1), select an inventory item from a drop down (Order Form - C6:C20) and then enter an order amount (Order Form - D6:D20).

When they click "Place Order", the Macro copies the Store Name and Today's Date into Orders - Column A and Column B last row.

I am trying to write a look that will look at Order Form C6, find the Order Form C6 cell value in Orders Column Headers I:DZ and then paste the Order Form D6 value under the respective Column at last row (same row where Store Name and Today's Date was pasted).

I am then looking for this loop to continue C6 to C20 UNTIL the value in C equals "Select an option below" or C20 completes.

The Order Form looks like this:

The Data Sheet is setup like this:

Any help or comments that can push me in the right direction would be EXTREMELY helpful. Here is the code I have so far. I have commented out where the loop should be, my brain just can't figure out the process.

Sub Submit_Order()

' DIM variables
Dim LastRow As Long
Dim Retailer, OrderDate, Amount, OrderTakenBy, ExpectedDelivery As Range

'Set value on variables
Sheets("Order Form").Activate
Set Retailer = Range("C3")
Set OrderDate = Range("H1")
Set Amount = Range("H33")
Set OrderTakenBy = Range("F1")
Set ExpectedDelivery = Range("H3")

'Paste sheet values by last row
Sheets("Orders").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(LastRow, "A").Value = Retailer
Cells(LastRow, "B").Value = OrderDate
Cells(LastRow, "C").Value = Amount
Cells(LastRow, "D").Value = OrderTakenBy
Cells(LastRow, "E").Value = ExpectedDelivery

'Work your way through C6:C20
' For Next
'   Dim Rng as Range
'   Set Rng = 'Order Form'!C6 'Range is the beer choice for header'
'   For Rng <> "Select a beer below"
'   Index C6 and find in sheet Orders Range I1:DZ1 to set Column
'   Find C3 and H1 and match Column against sheet Orders Range A1:B1
'   Paste D6 at the intersection
'   Move down one row sheet Order Form and range C6 to C7
'   Set Rng = Offset (Rng,1) to move to Set row above
'   Loop through, repeating until until C6:C20 value = "Select a beer below"
' End Loop

End Sub

 

[Legacy 143009]

How about to use good old Match function Paste this code after you insert date value to Orders column B:

  Dim optionsRange As Range, rng AS Range
  Set optionsRange = Worksheets("Order Form").Range("C6:C20")
  With Worksheets("Orders")
  For Each rng In optionsRange
    If rng.Value <> "" And Not IsError(Application.Match(rng.Value, .Range("1:1"), 0)) Then
      .Cells(LastRow, Application.Match(rng.Value, .Range("1:1"), 0)).Value = rng.Offset(, 1).Value
    End If
  Next
  End With

 

[Gliffix101]

How about to use good old Match function Paste this code after you insert date value to Orders column B:

  Dim optionsRange As Range, rng AS Range
  Set optionsRange = Worksheets("Order Form").Range("C6:C20")
  With Worksheets("Orders")
  For Each rng In optionsRange
    If rng.Value <> "" And Not IsError(Application.Match(rng.Value, .Range("1:1"), 0)) Then
      .Cells(LastRow, Application.Match(rng.Value, .Range("1:1"), 0)).Value = rng.Offset(, 1).Value
    End If
  Next
  End With

Look at this guy coming in here all like, "Oh - let me take your over complicated thought and make it SUPER easy that even a 5 year old could do it."

This is a fantastic solution that worked better than I could ever dream!! You, my friend, are a gift from the heavens!

 

[Legacy 143009]

You are welcome Thanks for the feedback