Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Simple query, but is there a simple answer?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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???


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    [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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  10. #10
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •