1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Question: How to run same script in multiple sheets

Discussion in 'Business Applications' started by Brian Rohan, Apr 6, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    Hello Everyone,


    I would like to thank Bomb 21 for the help I received with the Excel spreadsheet earlier. I have 2 other dilemmas that I need help with. I am not experienced at all with VB. I searched on the net for a basic answer but all I could find were answers that did way more than I needed. Here it goes. (Please see the attached zipped spreadsheet)

    I have a spreadsheet with a VB script that mostly runs as I would like on the first sheet. I would like this script to:

    1.) I would like the script to calculate the results it gives me in column D as follows, count the number entries that range in price from $0-$199,999, $200,000-$249,999, $250,000-$349,999, $350,000-$499,999, $500,000-$749,999, $750,000-$999,999, $1000000+ and place those into their corresponding place holders in column G (for each section, Active, Pending, WIthdrawn, Sold etc.)

    2.) Perform this script on all of the sheets in the Spreadsheet.


    Thank you in advance for your help with this.
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi again.

    Sorry, this cannot work. See:

    If Cell = " DETACHD" Then
    Range("C" & Rows.Count).End(xlUp).Offset(1) = WorksheetFunction.Trim(Cell.Offset(-1))
    Range("C" & Rows.Count).End(xlUp).Offset(, 1) = Cell.Offset(7)
    ...
    ElseIf Cell = "Canceled " Then
    Range("C" & Rows.Count).End(xlUp).Offset(1) = "Canceled"


    , that's during a scan of column A. But (ignoring that the syntax is wrong) the categories (e.g.Canceled) aren't within the "records" themselves, they're only in summary rows:

    957 "Canceled"
    987 "2 Canceled"
    989 "Total 2 Canceled Average DOM: 25 Average List: $169,900"

    Even if the categories were in the "records" it wouldn't work because (e.g.) running the code for sheet 11 gives you $129,900 - $139,900 in D11 which Excel can't fit into any of your brackets.

    Rethink required, I'm afraid. :(
     
  3. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    But all is not lost as -looks like this data has been pulled out of a database, perhaps you'd be better off performing your calcs on said application. If Access, I know there'll be folk here aboout to chew you hand off to know what you're after.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    This is a continuation from another thread, kind of. The data is copied & pasted from t'internet. Thus pretty much all is lost. :(
     
  5. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744

    Ah, I see. :(

    I would suggest a helper column next to values extracted from the loop marking out the values to those headings: Active, Sold, Bump etc.

    Then use a SUMIF for the summary baskets. Finally loop through all the sheets doing the same thing.

    But that's not good if what bomb has said above is not addressed.
     
  6. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    I don't need the ranges, I need either the high or the low number. Can column D be parsed to remove one of those numbers (ex $100,000 - $110,000) be changed to either 100000 or 110000. I am noticing that the entries that have a range also have $ where as the other entries do not, will that help? Again I do not know the VB very well but could you search column D for cells that contain $, and get the info from there somehow?

    If you can not do that what would the possibility be of just deleting those entries as a last resort? Then to tabulate the results, can you then search column D from the beginning to the first cell that contains text, (the first section will ALWAYS be active, then the next section will ALWAYS be Bumpable, etc) extract that info (even to a seperate column) and perform your counting on just that data, then put the results into the correct cell in column G.

    Does this help at all?
     
  7. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    BTW remember that there is already a script in place that already sorts the info form column A to more refined results into column D.

    Thanks again
     
  8. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    Actually one other item, can we make that script run for all of the sheets, instead of just the one that is "active" sheets 11,12,13,14,15,20 at the bottom of the page, that would be a great step as well.
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I remember, since I wrote the draft. But let's check if you mod does what you think.

    1. Add this line:

    Range("C1") = "Active"

    after this line:

    x = Range("A" & Rows.Count).End(xlUp).Row

    2. Delete this line:

    Range("C1:D1").Delete Shift:=xlUp


    Now run it for sheet 11. Now, I can find this in column A:

    71 Active

    and C73 = "Canceled". So, are you positive that D2:D72 are the (correct) "Active" values?
     
  10. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    I had written that there was a script for the other person that had responded. I am very grateful that you wrote the original draft for me.

    I did what you had said, I don't see where the number 71 shows up however I do see that cell C1 does now say active. You are correct that there are 71 entries that are 'active" C72 - the title cell gives 71 total, which also means that the D2 - D72 are the correct "active" values.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    What is the other code? What does it do? How do you get this data from the internet? If we can get a good data structure returned from the data set, we could do this easily with either a few formulas or a pivot table.

    Also, in the file you posted in this thread, in what stage is that? After download from the internet? Prior to running code you have (from somewhere?) to re-format the data?
     
  12. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    As an FYI, for sheet 11, the numbers for active that would go in column G for the Active heading would be

    0-200 34
    200 - 250 11
    250 - 350 16
    350 - 500 5
    500 - 750 3
    750 - 1000 1

    If that helps
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    So the range of values you want to see vary for different sheets?
     
  14. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    The data from the internet can not be put into the spreadsheet in any other manner than it already is in column A. Bomb #21 has made the script that parses that data perfectly into later columns. You can see this script by hitting ALT F11 to get into the Microsoft Visual Basic with the spreadsheet open.
     
  15. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "I had written that there was a script for the other person that had responded."

    I know.

    "I am very grateful that you wrote the original draft for me."

    I know that too.

    Relax. I just don't want your data to be train-wrecked. :)

    Remind me on one thing. It IS OK for column A to be deleted once the IDs and values have been parsed to B & C, yes?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/816297

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice