Other workbook, Range condition, grouped, and sum

[James580]

I need a VB code that will look into another workbook, look into a column and sees a “1” then stays on that same row and look for a code “NI” or “M4” then take the total number of that group. It needs to go to the next row and do the same thing, once done any column with a “1” that matches the set of group codes, and adds all of them

User Form
User needs to put in ST(Date) {file name} -

I am looking for a command button (Stops) once pushed will go to the file (ST(date)) attached file name is st82708

once in the file it needs to look at Trans_type (1) then look at item_code (list would be a list of codes.

VAC would have a code of HT, PP, SS, TR, TW, VA, VW. it should look at Num_items and take all the codes numbers and total them up for the STOP, VAC Group and return a number for VAC, then the same would have to be done for Price Group, codes as BC, CA, CC, CU, EC and return the Price Group total number, then anything left in the Trans_Type (1) would have a Group call "Others" this would be anything that is left.

[rylo]

Hi

I think that you can do these pretty easily with functions. Using workbook STS82808 try the following

M1: VAC
M2: Price
M3: Other
N1: =SUMPRODUCT(--(A2:A93=1)*((D2:D93="HT")+(D2:D93="PP")+(D2:D93="SS")+(D2:D93="TR")+(D2:D93="TW")+(D2:D93="VA")+(D2:D93="VW"))*(F2:F93))
N2: =SUMPRODUCT(--(A2:A93=1)*((D2:D93="BC")+(D2:D93="CA")+(D2:D93="CC")+(D2:D93="CU")+(D2:D93="EC"))*(F2:F93))
N3: =SUM(F2:F93)-N2-N1

You could use code to create the formulas, and have them automatically adjust for the data range.

Does that help?

rylo

[James580]

I like the macro better only because STS82808 changes every day

[James580]

Can anyone help me with this?

[rylo]

Hi

You don't (to me anyway) clearly advise what you want to do with the output. You could use the code to write the formulas in the workbook, with variable references as required.

You do talk about a form. Do you want to bring back the results to the form somewhere?

How about giving some more detail on exactly what you want to do - perhaps detail the steps you would take manually to perform your process.


rylo

[James580]

There is a report that is do every weekday morning, this report has parts from other departments reports. I need to take infromation from these reports and build two reports, a master list so I can use this information any way I need and the current report templete.

the other departments reports will be saved to a folder, with the departments name and what day it is (Mon, or tue) this is because we don't want the file overwrite because of the weekend.

One report give statue of the center locations, there are 9 centers I need to combined (only numbers) these numbers from each file into the master file.

There are five other department reports, each has information I need

I would like a user form that would have me put in the (tue or Wed...) and pull all files that the file name ends in that day. the 9 center informaiton needs to be combinded first (maybe).

There are four other reports, they have dates at the end of the file name. I would like to put in the dates and have the files pulled and information dumped into the master field.

Once all the information is dumped into the master file, I would like a button on the user form that would build the final report.

I still need to do the above part but that is one of the date files. if you would like to talk better to person let me know and maybe I can explain better, I have it in my head just cant explain it in type message

[rylo]

Hi

Small steps. The files that end in a day - do they always have the same starting parts, and only the last 3 chars are different? If so, then what are the names? If not, then what is the name format?

rylo

[James580]

Grove Park Down Report
West Down Report
East Down Report
Westmoreland Down Report
Lakeside Down Report
Hanover Down Report
Gayton Down Report
Tri-city Down Report
Byrd Down Report
Brandmill Down Report
Airport Down Report

Production Mailing Room Report

Transportation Report

IVR & APAC

behind each report name is the Day (Tue)

I need the down route information combined from Cells (E20), (F20), (F25)

[James580]

Other files

[rylo]

Hi

Save the attached file in the same directory as your source files, then run the macro.

You may have to correct the file names in column A, as the one file you attached had a different name from the list you gave.

This should create a series of formulas that are linked to the required cells in the input files. Once this is done, you could copy paste special the block to where ever you need to store the data.

Does that get you started.

rylo

[James580]

I really didn't understand what it was trying to do.


I'm not sure if this is against the rules but if you could call me or give me a way to call you and really explain what i'm looking for that would be great.

804-475-1337 is my number anytime is good.

[rylo]

Hi

How about you put up an example of your output workbook - the place where the input data from the daily files is supposed to go.

What I gave you was a way to link to the daily files and bring in the relevant data you required. You haven't advised what you want to do with that data.

rylo

[James580]

Still no luck, I can't seem to get it to look for the files

[rylo]

Hi

Put this file along with the ones you have zipped in the same directory.

Open this file and run the macro. You should find that there are links to the first 6 file. However, the last one doesn't have the same format - it doesn't have a day at the end.

See if this now makes more sense.

rylo

[James580]

This is the second part we talked about, if you can help me with some of them I can't do the rest once I get the code.

I put comments in the cell on what each cell needs or means. I tried looking but can't find any codes that will give me what i'm looking for.

[James580]

guess adding the file could help

[rylo]

Hi

So where are the files if they are not in the same place as the master output sheet? Are they in a fixed location? If not then how do you expect it to know where they are? Do you want to have another box to put in the path? Or are you going to put in the full file name and path? Also are the sheet names going to be consistantly the same name as the file????

Can you please be more detailed and specific in what you are trying to do. We cannot mind read, and your comments are just too broad to work on.

rylo

[James580]

See attachment for how files are setup.

each folder is a department, each department has a form with a command button. The Command button says the file to the department folder with the day as part of the file name (Tue). These files will be in the same place ever time. I would like to change this and have the file save with the date 10108 and once you input the 10108 it pulls that departments file.

to your question

"? Do you want to have another box to put in the path? Or are you going to put in the full file name and path?"

Can it have the file path and I just put in a date since the file name will never change only the date?


to your question

Also are the sheet names going to be consistantly the same name as the file????

I don't know what to say to the question, if it needs to be then I would need a code that would name the sheet after the file name.

I'm sorry, that i'm not giving all the details you need, this is a big task and i'm trying to see what parts I can do, and what parts I really just can't touch.

[rylo]

Hi

Sorry, but this is just confusing me more and more.

1) Masterfile is M:\Cir at a glance\master file\masterfile.xls.
2) Draw files are M:\Cir at a glance\DSI reports\dra82[7,8]08.xls
3) Same for the STS and Year Complaint files.

So where do the files with the week day come into it? And how do you work out what the department is you are chasing?

Can you be much more specific and detailed. Perhaps go through the steps for a worked example, giving the file structures, what workbook is required, how it would be found etc

rylo

[James580]

1 Correct
2 Correct
3 Correct

So where do the files with the week day come into it? Going to change those to the date and not week day but they are pulled into a master list which you built. I can save the data longer if I use date.

How do you work out what the department is you are chasing? I do not understand the questions; other then by report name/date and the cells in each form.

Report Name 100208


Each department fills out their form, each form as a save button that will save the form in that’s Report’s folder. Each form will be saved by report name, and date, I would like a user form that will pull information by date from each of the saved forms and save into a Master List. With the master list different charts and reports can be run.

So

A report is run out of our system and saved as STS82808 (report type and date), on the Masterfile you will see File name STS82808. I would like the user to input date only and have STS already there for the search. The user would push the Start button and all fields before will be filled in by the criteria and from file STS82808. Same would be done for Stops, and Verafast



Same thing for STS82807 (which is wrong in my example I sent you)

[rylo]

Hi

Here's the start of things. Change the path from C to M and see if it gives the right numbers for the 4 fields filled.

Please Login or Register  to view this content.

If it does, then see if you can expand for the other items. If you can't be sure of the SUMPRODUCT formulas, then see if you can work them out in the spreadsheet directly, then convert them to code.

If it would be easier, you may just want to create the SUMPRODUCT formulas, then build code to change the file name with replace. May be easier to upkeep that way.

rylo

[James580]

How do I get it to look for two Item_code and sum them together

under VAC the Item codes are "VA" "RD"

same with VAC on Stops file STS82708

HT, PP, SS, TR, TW, VA, VW

Please Login or Register  to view this content.

[rylo]

Hi

Here's one for F12. Due to the size of the formula, I've had to put the formula directly into the spreadsheet, then turn it into a value.

Please Login or Register  to view this content.

rylo

[rylo]

Hi

Thinking about this, it may be better to use a database approach to this. In the attached code, I've treated the spreadsheet as a database, and written some sql to extract your data. Much easier to maintain, and read. I've only done it for F12 and F13 given the complexity of the selection criteria.

Please Login or Register  to view this content.

rylo

[James580]

Since the file will change everyday should sts82808 be in the code or should it be the textbox1.value?

Please Login or Register  to view this content.

[James580]

I need this code to also look at trans)type 3

Please Login or Register  to view this content.

How do get it to search in the trans_type 3 also and sum the numbers

[rylo]

Hi


Yep, realised that I should have put in a variable in the file name. I've done some more to show how different items are combined. I've also changed the reference file for some of the Column D items.

Also, the comment you have about "adding for all the items not used" is pretty broad, so I've put in something that will fill in the total cells. Then you can pur a formula in the "other" item that will be the total - all the items not specified. Much easier to manage.

Please Login or Register  to view this content.

rylo

[James580]

I need to get Previous Year from another Year Complaint Workbook depending on what date is put in the text box. I tried this three different way but nothing.


How do I get Home Deilvery to look at Draw82808 and look at Col1_copies (135785) and add to Col2_copies (1088) and Col3_copies (887) and give me the sum for "C42" . Also how to just pull out Col6_copies (664) and put in "E42"

What would be the code just to go into a pull information for different cell, I have something like that for Down Route Reports, but it does not return the data into a picked cell and that is what I will need.

So C2 from DRAW82808 and input it into B62 on Master List

[rylo]

Hi

1) What ways have you tried for the previous year?

2) Use a union query, or put each of the items into separate variables and add, or clear out C42, then add each of the returns to the value already in C42.

3) Go back and review some of the earlier responses. You have been given code to either build a formula to the relevant file, or open the file, extract the data, then close the file. You don't have to work with the same data extraction method for all the items - it just seemed to me to be the easiest way to get the data for that group with that file structure was to use SQL.


rylo

[James580]

I tried to pull it like the STS complaints but I can understand part of the code and what it means/doing.

I tried to pull it like I did for the Down routes, but that doesn't insert it into a cell

Some of the codes I understand and some have to much going for my beginner self. I do play with some of the codes to see how they work or I change something and see what happens so I know what that line does, but some of these just give me errors and I don't know why it wont work.


I have read Question/answer 2 many times and not really understanding what you are saying. Have we done something like that already? If so can you tell me on what and how we used it.


What is SQL?


I tried to use this code and change a few things but I can't get it to work. I tried giving the date a header of Month and the number row "Comp" as a header (Year Complaints).

Please Login or Register  to view this content.

[James580]

I tried this code and only got back #value!

what do I have wrong

Please Login or Register  to view this content.

I have attached the two files

[rylo]

Hi

1) Change the line to

Please Login or Register  to view this content.

2) Make sure the file date formats are the same (ie mm/dd/yy)

rylo

[James580]

Can you tell me what you changed and why (so I can try to understand how the code works)

[rylo]

Hi

Easiest way to see the differences is to put both strings one above the other to see the changes. I can remember:

1) as the spreadsheet name has a space, it has to be enclosed with ' as in 'year complaints.xls'

2) You had a range of $b$3:$A$ which should have been $b$3:$b$

3) Look at the construct of the date. I used cdate to make sure it was looking for the date part of the string, then formatted to be the raw number not a pretty format. I've always found working with dates in code "awkward" to say the least. Ususally have to just keep fiddling until I find something that works....

rylo

[James580]

Why wont my code work

it gives me a debug at "Private Sub Savebutton_Click()" in yellow

Please Login or Register  to view this content.

[rylo]

Hi

my initial though would be that the file name is not valid. Try putting in something like "aaa" in the variable and see if it works.

rylo

[James580]

I tried and changed it to aaa

i'm using a user form to do this for IVR & APAC

[rylo]

Hi

Ran your code save_click but changed the first line to

Please Login or Register  to view this content.

and it worked OK. What error message are you getting with that code?

rylo

[James580]

Still didn't work,

Don't use the SAVE button on the main page, this is a user form

Please Login or Register  to view this content.

[rylo]

Hi

So how are you showing the form, what data are you putting into the textboxes??? Go through the steps so we can see exactly what you are doing. More detail required.


rylo

[James580]

attacted is the page print.

I would like the user to input the information and the cells are filled with this information.

The save button, take the report name IVR & APAC plus the date 101408 and save it in the folder M:\Cir at a Glance\IVR & APAC Report


once the save button has been pushed the file closes

[rylo]

Hi

1)

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

Do you know how to use the debug facility? If not find out as stepping through the code and looking at the variables as you go will help a lot when trying to debug code.


rylo

[James580]

I tried but didn't get it to work,

All the codes we have done so far have been done by columns I need a code that looks at row. I would like the code to look at a word(s) and then take the number to the right of that word(s) and then look for another word(s) and the number to the right of that word, and add them together and return the number to G45.

the master file for the returned values is in Yellow, if you can help me with the first part I can do the others

[rylo]

Hi

Any reason that you can't restructure your draw sheet so that the categories become column headings, and the numbers are placed in those columns?

rylo

[jhp580]

It's the way it comes out of our system

is there a way it can restructure itself then pull the information?

[rylo]

Hi

The following code assumes that the workbook is open, and the required sheet is active.

Please Login or Register  to view this content.

Given the previous code you have, you should have the workbook open and be able to activate it, run the code, and have it put the output into the required cell. At this stage, it only comes up in a messagebox.

HTH

rylo

[James580]

It worked great, but is there another way pull the information out and into a cell, with the file not being opened.

I have a command button on the master file, I would like it to look at textbox3 for the first part of the file name but not the .xls part, go to that file, then look for the "Home Delivery", "Carrier Copy", "College Routes" and then add them up and return the sum to c42 on the masterfile

[rylo]

Hi

Not easily that I can think of. You could use a heap of SUMPRODUCT formulas but you would have to cycle through each of your criteria for every column that could contain them. You can't use a full column for a SUMPRODUCT function, so you would have to either put in a large number to cover your size possibilities, or perhaps use SQL to work out the number of items in the file and use that to limit your ranges.

Why not just open the file, run the code, then shut down the file? If you turn off the screenupdating, then the user wouldn't know it has been opened??

rylo

[James580]

How do I get where Trans_Type = 2 to also look in 3

Trans_type = 2 it also needs to look for 3 and add together anything from 3 with 2

Please Login or Register  to view this content.

[Andy Pope]

Update your sql command

Please Login or Register  to view this content.