Code to Get Value from Form and Add to Total

[TFiske]

Hi all,

Very new to VBA.

We order large amounts of annual flowers for a city beautification program every year. The orders come in from citizens and groups that have adopted a flower planter around the city. I am trying to devise a faster way of totaling up the flower orders and keeping track of what each group ordered.

In the attached sample, the user would enter how many flats of each variety of flower by color in the table in "Flower Order Entry."

The Enter button on the "Flower Order Entry" would run a macro that:

1) grabs the values entered into the table and the group name from "Flower Order Entry" and basically copy/paste to the first available row in the "Total Flower Order" tab (Columns A:V).
2) adds up all the orders by flower type and color in AA4:AS26 of the "Total Flower Order" tab. The Sample sheet has expected values.

This is just an idea of how to do this. If anyone has another suggestion of how to build this, I'd love to hear it. Thanks!!

[ranman256]

If you enter data (shown in image), this is better for excel to summarize.
Once all the data is in this format, a pivot table will produce the list for everyone. (like your other sheets)

[TFiske]

I'm trying to automate the data entry process. In my first model, my goal is to reduce the amount of data entry to only the number of flats ordered. Going with your suggestion, is there a way to use my original form to automatically populate a table that could then be summarized with a pivot table?

[TFiske]

I did some research and came up with the following code:

Sub FlowerOrderWizard()

' Keyboard Shortcut: Ctrl+t
'
' Declares the ranges that will be copied and/or cleared.
Dim SrtRange As Range
Dim OrderRange As Range
Dim ClrOrderRange As Range

Set SrtRange = Sheets("Flower Order Entry").Range("B2")
Set OrderRange = Sheets("Flower Order Entry").Range("B4:W27")
Set ClrOrderRange = Sheets("Flower Order Entry").Range("E4:W26")

' Selects and copies the flower order range from "Flower Order Entry" B4:W27,
' including the "END" tag at B27 used by the loop to find the end of the table
    Sheets("Flower Order Entry").Activate
    OrderRange.Select
    Selection.Copy
    
    Sheets("Total Flower Order").Activate
    
    Dim loopBool As Boolean
    loopBool = True
    Sheets("Total Flower Order").Range("A1").Activate
    Do While loopBool = True
        If ActiveCell.Value = "END" Then
            loopBool = False
        Else
            ActiveCell.Offset(1, 0).Activate
        End If
    Loop
    
' Pastes the data copied from "Flower Order Entry" to end of the table
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
' Returns the user to the "Flower Order Entry" tab and clears the ranges for
' Group Name and the quantity of flats
    Sheets("Flower Order Entry").Activate
    ClrOrderRange.ClearContents
    SrtRange.ClearContents
    SrtRange.Select
            
End Sub

Then the SUMPRODUCT function uses some dynamic named ranges to total all entries by flower type and color in "Total Flower Order"AA2:AS24

=IF(SUMPRODUCT((RefRng_CommonNames=$Z2)*(Rng_FlowerColor=AA$1)*Ary_AllFlowerOrders)=0,"",SUMPRODUCT((RefRng_CommonNames=$Z2)*(Rng_FlowerColor=AA$1)*Ary_AllFlowerOrders))

If anyone has any feedback on this approach, I'd really appreciate it. Please see the attached sample.

[TFiske]

So you're suggestion is to manually enter the orders into a single table and then summarize with a pivot table?