adding code to existing code

[D_M_S]

The existing code searches targeted sheet and grabs the matching data and transfers it to specified locations.I need to add further code to the existing code so as to use the single userform.The sample sheet in grey color shows the information needed.
1. Can the code using the same date for all data transfer look in sheet "data" and search for products "Juice-red" and "juice-white" in column D and place the word "JUICE" in sheet 'Results'!M43 cell , 'Results'!N43 the actual combined time for "Juice-red" and "juice-white" from 'Data'!G;G, 'Results'!O43 the actual combined Volume for "Juice-red" and "juice-white" from 'Data'!F;F and when it finds the "Juice-red" and "juice-white" in column D it places in the 'Results'!P43 which shift had the product running

I do not honestly know if this request can be combined with the existing code or it needs to be a separate code.....can it be done?

Cheers

D_M_S

[Richard Buttrey]

Hi,

Yes that's possible but your code seems an unnecessarily complicated way of transferring a set of data from one sheet to a results sheet.

Use a cell with a drop down data validation list of sheet names, and another with dates, or simply a cell in which you enter a date. Then for your Results output area copy the relevant column labels into columns L4:P4 and name the output range L4:L18 say "MyDataOut"

Then all you need is a simple single line of code that uses an Advanced Data Filter macro with a criteria range that uses the sheet drop down cell as one criteria and the date cell as another.

e.g.

Please Login or Register  to view this content.

Similarly you can use another criteria with wild cards to pick up anything in column D that has the characters 'juice' in the string.

Advanced Data Filtering is by far the easiest way to achieve the sort of thing you're doing and is literally a single line of code once you've set up your range names for Data, Output and Criteria.

[D_M_S]

Hi Richard

Thanks for response

Would you be able to supply an example .. you solution may be way easier and more user friendly

Thanks

D_M_S

[D_M_S]

Hi Again

If it is possible I would like to see the VBA code needed for my existing code also for transferring the data.. are you able to post the code if I was to go with my original code for transferring data?

Regards

D_M_S

[Richard Buttrey]

Hi Richard

Thanks for response

Would you be able to supply an example .. you solution may be way easier and more user friendly

Thanks

D_M_S

Sure,

However can you first describe in narrative form what you expect to happen. This is easier for me than trying to work backwards from the code.

For example tell me what are the rules that determine which rows get extracted to the Results sheet. And since it appears there are three blocks of data on the Results is there any reason why these couldn't be in different sets of columns rather than underneath each other. If the number of output rows varies then it's always easier if we don't have to worry about one extract over running into a separate extract area.

[D_M_S]

Hi Richard

I placed a narrative in the attached sample in sheet called narrative

Thank you

D_M_S

[D_M_S]

Hi

Can a title/heading also show depending on which sheet selected...sample files has a sample on Results sheet..

Best Regards

D_M_S

[Richard Buttrey]

Hi,

Where are you getting the numbers in the tables on the Graph Info sheet and what are the references to QQQQ SSSS etc.?

[D_M_S]

Hi

The Graph Info sheet looks at the dates in sheets Data "A","B" & "C" and then sums up all the times for Product G:G ,column I:I for planned,internal,external and sums up each in column M for time...I have not added the formulas yet

Cheers

[D_M_S]

Hi Richard

Were you able to create the solution you mentioned in your post #2 for Data Validation etc..etc..also is it possible with post #9

Thank you

D_M_S

[D_M_S]

Hi Richard

Were you able to create the solution you mentioned in your post #2 for Data Validation etc..etc..also is it possible with post #9

Thank you

D_M_S

[Richard Buttrey]

Must admit I haven't tried yet.

However taking DataA as an example. Do we imply that D6:D13 are all 'Juice White' and that rows 5, & 14 are essentially 'header' rows. Putting it another way do we assume that the blank cells underneath each value could equally contain the value from the previous row. i.e. is this essentially a table where all cells could be filled in but you've chosen not to for ease of data entry?

For a data extract filter to work (which is what I had in mind), there needs to be a regular 2 dimensional table of data.

[D_M_S]

Hi

D6:D13 are not "Juice White" as the entries for these rows (cells) are located in column I . When entries are placed they enter into the next empty row available...entries are placed by rows so that depending on which type it is depends on which columns the data is placed.Data A B4:N4 are headers...

Product in B:H and Stops in I:N

Data placed into rows and depending on whether a Product or Stop depends on which columns have data.
Example: In row 6 as it is a stop only columns B:C are needed on the product side and I:J:M:N on stop side...there will be empty cells

Thank you