userform command button to run code

[scouse13]

please see attached sheet, I need the code to find and total matching records in column T based on the selections made on the 2 comboboxes

combobox1 = week no
combobox2 = shift

all help most appreciated
scouse13

[harrisonland]

Please have a look at the attached... I've coded your userform's initialisation so it dynamically populates when it's opened... That way, you don't need to have an error handler in case there's no match.

CBx2 populates and becomes active once a CBx1 value has been selected.

Hitting "Search" (as you've labelled it) plonks a sumifs() formula into A1 on "waste" to do the calculation, and finally replaces it with a fixed value

You'll note I've changed the codenames of your "Records" and "waste" worksheets to make it easier to reference them in the code. I also changed your data to be in a table as I imagine these are data that will grow over time. It's not necessary but makes things a bit easier, too.

I was just a bit confused where these figures

a1=
If matches week38 and shift A =229.6
if matches week38 and shift B =59.9

come from, so you'll have to tweak the "=sumifs(....) formula to suit.

HTH

[scouse13]

Thanks Harrissonland, sorry its taken a while to get back to you, but been working, yeah that all works fine, the only issues I have is you have changed part of the records sheet to a table, 1) I have other code that runs producing results via the records sheet will that be ok, or will I need to change that code 2) can you show code without changing to a table? 3) if not can the table cover all columns and then I can change to code on other buttons to suit what results I need?

All figures in the records sheet come from a userform data sheet that users enter data on, 90% of data will be entered at the time but there is an edit but to add and change a records details

thanks

scouse13

[harrisonland]

Ooops. My table was only a section of your data, but never mind...

1. Other code will be fine anyway, you can still reference the cells in a table in the normal manner
2. See below!
3. Creating a table is as simple as selecting the range you want to include, then hitting [CTRL+T]; the benefits of using a table are discussed extensively elsewhere, but it can be moved about, columns and lines can be inserted or deleted without the code or other references breaking. Whether it's worth it for your application depends how you anticipate it being used, really.

You could simply replace the table notation with specific ranges, like this.

Please Login or Register  to view this content.

It does mean that you have to search for the last used row in each function, rather than using the whole column to iterate through, which is what you'll see up there^^^.

HTH
Tim

[scouse13]

thanks harrisonland will check the new code out, am sure it will work fine and hopefully make it a bit easier for me to change to what other searches I want to do, will let you know and add to reputation.

thanks for your help

scouse13

[scouse13]

HiHarrisonland, run the code before but I am getting " A value in the formula is of the wrong data type" error in the waste a1 cell, any ideas

Ooops. My table was only a section of your data, but never mind...

1. Other code will be fine anyway, you can still reference the cells in a table in the normal manner
2. See below!
3. Creating a table is as simple as selecting the range you want to include, then hitting [CTRL+T]; the benefits of using a table are discussed extensively elsewhere, but it can be moved about, columns and lines can be inserted or deleted without the code or other references breaking. Whether it's worth it for your application depends how you anticipate it being used, really.

You could simply replace the table notation with specific ranges, like this.

Please Login or Register  to view this content.

It does mean that you have to search for the last used row in each function, rather than using the whole column to iterate through, which is what you'll see up there^^^.

HTH
Tim

scouse13

[harrisonland]

Hi there. I've just pasted this into the worksheet after removing the table - it seems to work as intended, so I'm not quite sure what the issue was.

I've attached it here for you to look at...

Tim

[scouse13]

thanks harrisonland, will have a look, I have been playing around with table today as well and sort of got my head around that, just a final thing if you don't mind, working on the set up now of week no, and shift which produces the waste loss, if I wanted to show all shifts what would I need to add.

ie: choose week no, and choose all shifts, show the results will show the waste loss for shift A, B, C, D, E

I should with that do most thing I need to

scouse13

[harrisonland]

This formula will add up all values in column T for a week number stored in A1:

Please Login or Register  to view this content.

Instead of generating the figure via a VBA routine, you could just pop that in a cell and have the worksheet update automatically.

Alternatively, you could go the route similar to your week/shift analysis and just use an inputbox or another userform to get a week number from the user.

I've attached another version demonstrating different approaches on the "main" worksheet. (I also added a bit of error checking/handling in the userforms.)

Tim

[scouse13]

thanks Harrisonland for all your help, think I have my head around it now to progress further, but all works fine

thank you

scouse13

[harrisonland]

No worries - and thanks for rep.

Tim