Lookup and insert rows

[BJS]

Hi,

I am building an estimating tool.
I have a drop down box that can select multiple values,(jobs). let’s say one of those is “build a shed”
“Build a shed” as 10 associated tasks allocated to it, and other jobs have a varying number of tasks

Once the drop down selection has been made I want a macro that will count the number of tasks against the job and then add those number of rows underneath that drop down then I want to auto populate with the tasks details involved with “Build a shed”, or it can copy the selection from the job and insert direct into the sheet.


Jobs Tasks Duration (Hrs)

Build a Shed A 8
Build a Shed B 7
Build a Shed C 6
Build a Shed D 5
Build a Shed E 4
Build a Shed F 3
Build a Shed G 2
Erect a Fence A 2
Erect a Fence B 6
Erect a Fence C 8
Erect a Fence D 8
Erect a Fence E 2


If there is an easier way, I’m all up for easier.
Any help would be greatly appreciated.

[SamT]

How much VBA Coding experience do you have?

[BJS]

Hi SamT
Low to medium experience and it has been a few years since I have done any VBA.

[SamT]

A few years ago I was working on an estimator similar to your idea. My idea was to Use a UserForm for initial input with the first ComboBox (AKA Multi-column dropdown. Example: cmboJobs) to access the list in Column A, of the attachment. The code would then Find across Row one for the Item selected (Example: "Foundation") in cmboJobs and assign the Range associated with "T3" to the next ComboBox .

cmboTasks.RowList = Rows(1).Find(cmboJobs.Value).CurrentRegion

In the Next ComboBox (cmboTasks) when the User selected an item (Example: "Trenching") the code would Find down the same column that "T3" was found in for "TTrn" and assigns the found CurrentReqion to the next ComboBox.

The trick is that VBA UserForm ComboBoxes can have multiple Columns and some can be hidden from the User, but all can be used by Code. The first Column will be the Value of the ComboBox, but can be hidden from the User. I was going to use the third column for subsidiary WorkSheets when present.

Note that I am not talking about Form or Control ToolBox controls as found in the Excel Menu. I use the controls found in VBA when you Insert a UserForm in the Project Explorer Pane.

[BJS]

SamT
Thank you. Just need to get my head around this, but it sounds good.

Cheers