Simple query, but is there a simple answer?

[RET79]

Hi,

I have a macro, that is designed to extract chosen values from one worksheet, and put them on a row together on another worksheet. I have set up a simple input spreadsheet, where I would like the user to specify which cells he/she would like to extract the values for.


I want it so that an user could just enter B4, B5, B6 etc. in one column on the input sheet(ie the range would need to be dynamic) and then the macro would extract the values from those cells specified, then put them on one row(with no gaps between them) on another new worksheet.

Hope this makes sense,

RET79

[ This Message was edited by: RET79 on 2002-03-26 11:06 ]

[RET79]

So for instance, on the input sheet the user may type in the following in a column

C:MySourceFile

C4
D56
E23
F24
T3

Then, the user would click the macro button, and the macro would extract the values in the cells specified above from C:MySourceFile, and put them on a new sheet on the first row, so you might get this on range("A1:E1") of the new sheet:

32,543,24,124,253

But, it has to be flexible so that the user can specify however much cells, possibly hundreds and it still would work. They would just have to specify the cells and click a button.

Hope that made things clearer

thanks,

RET79

[Tom Schreiner]

Hi
You could place code in the Change_Event which would negate the user from having to type anything in at all.
They would just need to click on a cell or range of cells and the values could be sent programmatically.

Give some more details???

[RET79]

The question I posed was just one cog in a much bigger wheel. No, as my overall task is so complicated (and I won't go into that just yet) this would not be beneficial at this stage I'm sorry.

[ This Message was edited by: RET79 on 2002-03-26 11:42 ]

[RET79]

On 2002-03-26 11:20, TsTom wrote:
Hi
You could place code in the Change_Event which would negate the user from having to type anything in at all.
They would just need to click on a cell or range of cells and the values could be sent programmatically.

Give some more details???

What more details would you like?

If you have a solution with the change event thing I will like to see it, don't get me wrong. It's just that in my particular case it would be more beneficial to have them typed in a column. The reason being that I am actually extracting from a multiple of files, and, because not all the files are quite in the same format, extracting the value of cell B56 from the january file is equivalent to extracting the value from B58 from the april file is one example. I hpoe now you see why clicking the cells would not actually benefit my task much as I would have to open so many different files and click to acccomodate a change in format.
Thanks,

RET79

[ This Message was edited by: RET79 on 2002-03-26 12:28 ]

[Tom Schreiner]

Hi
I just do not understand what it is you are trying to do?

You want a user to type in a filename and ranges and have the information from those ranges extracted from that file and placed on a sheet in the current active workbook???

How do they know which cells to choose?
How many of these files are there to choose from?
Where is the information going exactly?
In what order?
Is the data destination accumalative or new after each search?

Maybe I'm extra dense today?

Post as much info as you are willing to post. The more clear it is to others what you are trying to do the better chance of getting some great help.

It appears, from what I can gather, that you should create a template for each file that you will be querying. Then put hyperlinks or buttons on a worksheet for a one click job.

I'll wait and see what you repost...
Tom

[ This Message was edited by: TsTom on 2002-03-26 12:47 ]

[RET79]

OK, let me tell you the story behind it and maybe this will make more sense to you.

But before I do, yes, I want the user to be able to write down a file link, then specify cell reference , and then click a button where the macro would pick out these values for them and put them neatly on a row on a new sheet.

The story goes like this:

My boss told me he wanted me to extract the cell values for B5, C7, D10, D14 from this January spreadsheet. (he gave 20 cells actually, but let's use 4 here for siimplicity).
He wanted to see them in a row, next to each other, as he wanted to compare the values. OK, so easy enough so far

However, columns B,C and D represented the data for 1st Jan 2001. Columns E,F,G contained the equivalent data for 2nd Jan 2001, so I also had to pick out E5, F7, G10, G14 and so on accross the spreadsheet until the data ended with the 3 columns for 31 Jan 2001. So, in the result sheet I woudl have 31 rows, each containing the daily data. Ok, so that wasn't too difficult to do, just needed to write a clever loop with offset(0,3i), where i was number of day in the month, no problem.

However, he had a file for every month, and wanted to extract these equivalent cells from all 12 monthly spreadsheets, so that he had the data for every day of the year for the values of this particular fund. This was fine, just had to loop through the 12 files, no problem.

However, there was a hitch in that the files weren't all quite in the same format. For instance, the B5 cell from the January file was not equivalent to B5 from the April file, the April data for column B(for whatever reason) was moved down 2 lines, so I had to pick out B7 instead of B5 etc. However, funnily enough, the format for July-Dec was consistent with the January file, so no change was required here. But April, May and June were inconsistent with the original file, so we had to pick out slightly different cells.

Therefore, instead of hard coding for every month, I decided it would be a good idea to set up an 'input sheet', where the user would specify which cells to pick out from which files, so as to avoid the user going into the code and adjusting the arrays.

Now, with that bit of background I hope you can see why I required a solution to the simple problem of letting an user pick which cells, from what files he/she wanted to be put on a row in a new sheet.

For instance, maybe something like this

Month applicable:
January
Feb
March
November
December

C:FundValueJan.xls
C:FundValueFeb.xls
C:FundValueMar.xls
C:FundvalueNov.xls
C:FundvalueDec.xls

Need to extract values from the following cells:

B5
C7
D10
D14

Then, in another column you would have this

Months applicable
April

C:FundvalueApril.xls

B7
C7
D10
D14

and so on for all different months.

When replying to this post, please concentrate your efforts with what I just mentioned, don't worry about the offset(0,3i) bit since that is already sorted out in my existing macro. What I am most concerned about is setting up an easy to use input sheet where the user can specify which cells , from which files he wants to see and then display the values in a row on a new sheet - one row for each day.

Thankyou,

RET79

[RET79]

[quote]
On 2002-03-26 12:44, TsTom wrote:
Hi
I just do not understand what it is you are trying to do?

You want a user to type in a filename and ranges and have the information from those ranges extracted from that file and placed on a sheet in the current active workbook???

>> Yes, basically, that's the jist of it.<<

How do they know which cells to choose?

>>These will be known, are usually the same for most files but will be slightly different for some files to accomodate for slightly different format.<<


How many of these files are there to choose from?

>>> 12 files, one for each month<<

Where is the information going exactly?

>>The information goes onto a new worksheet. One row for each day, so the end result will have 365 rows. Data for first day of month will be in columns B,C,D from each file. Data from second day of each month will be from the same rows, but from columns E,F,G. 3rd day H, I, J and so on until the last day of the month, which varies from month to month of course. But don't worry too much about that, that is already sorted out with my macro. You only need to specify the Cell references for the B,C,D columns as the rest of them are just offset(0,3*i) from these cells every time.<<

In what order?

>> Ideally it would be great to get the 1st row of the result sheet showing the data for 1st JAn 2001, and the 365th row showing the data for 31st Dec 2001. But this should be easy by doing a sort I think, so no need to worry what order the data form the files go in e.g. it wouldn't matter if you put in January, then April's data say as one of the cells referred to is the DATE so the dates will be there on every row so sorting is not a problem.<<

Is the data destination accumalative or new after each search?

>>New after every search. Data for 1st Feb 2001 would be inserted in row 32 of the result sheet.

Maybe I'm extra dense today?

>>Maybe it's me that's too barmy!

And yes, this should be reduced ultimately to a one button job once the user has specified exactly which cells, from which files he wants to extract values for.


Thanks

RET79

[Jack in the UK]

OK

you sent me a private message to look at this. thisis my opinion

ive read some 5 times and conclude

forget the dos donmt wont wonts.

you need a user form finish.

MORE

the user form needs to do the majic

filemane to locate
cells to transpose

nothing else?

thats it or do i misunderstand.

IF SO:

design a userform that ask for data and the data has scripts attacherd to say copy and paste.

thats it problem soleved....me hopes

PLEASE ADVISE.

[Russell Hauf]

On 2002-03-26 11:15, RET79 wrote:
So for instance, on the input sheet the user may type in the following in a column

C:MySourceFile

C4
D56
E23
F24
T3

Then, the user would click the macro button, and the macro would extract the values in the cells specified above from C:MySourceFile, and put them on a new sheet on the first row, so you might get this on range("A1:E1") of the new sheet:

32,543,24,124,253

But, it has to be flexible so that the user can specify however much cells, possibly hundreds and it still would work. They would just have to specify the cells and click a button.

Hope that made things clearer

thanks,

RET79

I would use a Form, like Jack said. On the form I would have a button that, when clicked, would bring up the Open File dialog (Application.GetOpenFileName). This file name would go into a label after the user picks her file (not a text box, so the user could not change it). I would also put a RefEdit control on the form to select the cells that the user has typed the cell references in. So, while they would still have to type in the cell references, the users would not have to type in the file names and you would also be able to pretty much make sure that the file exists.

Hope this helps,

Russell

[ This Message was edited by: Russell Hauf on 2002-03-26 15:06 ]