Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post

    Recreating a data display table using VBA

    Howdy folks,

    My project (http://windowssecrets.com/forums/sho...l=1#post893492) continues. My users were so impressed with what the macro could do (and that's thanks largely to help I got here!) that they've (as usual) asked for more functionality.

    One item has me stumped. In the attached file there's a data display table X15:AE39. Column X pulls the compound names from Column A and then does the calculations in Y through AE. They've asked me to recreate that table each time they download data, but the number of compounds in that table will differ each time. What's the best way to populate that column and have it stop when it reaches the last compound? I have a sneaky feeling it's going to be that OFFSET function again, isn't it?

    Oh, and my plan is to just populate Column X and then delete the extras (notice that it only display each compound once, even though each compound is in Column A twice). Is there a "better" way to handle it?

    Thanks in advance for your help.

    Beej

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Bee J
    Have a few questions. I will refer to a "section" as how you have it setup (Processed Tab) as a group of compounds in column A with its related data in adjacent columns to its right and isolated by blank rows.

    1. With new downloads, will the table (X15:AE39) always pull the compounds in the X column from the second section of compunds as they are doing now (Currently A19:A40)?
    2. Is your goal to have a table for each section of compounds starting in the X column?
    3. With new downloads, will the sections expand and/or shrink (# of rows, # of columns)?
    4. Will the number of sections of componds (currently 4) ever change?
    5. Will the downloads present new names of compunds or keep the same existing names?
    6. What range of data will the new download effect?

    Maud
    Last edited by Maudibe; 2013-03-03 at 06:56.

  3. #3
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    Hi Maudibe,

    Thanks for responding.

    1. Yes.

    2. No, I only need a table for the compounds whose name starts with "SGE". The EC and Inhibitor lists don't need to be pulled in (they are used to do the calculations within the table instead, and I have that under control).

    3. Yes, the number of rows in the "SGE" list will shrink or expand. The other lists (ECs and Inhibitors) will stay the same. The number of columns will always be the same, which means this data table I'm trying to create will always start in X15 and will run to AEnn, where nn is determined by the number of compounds in the SGE list. Also, and this might help, there will always be blank rows between the SGE list and the Inhibitor and EC lists.

    4. No.

    5. The names of the "SGE" compounds will change. The other names--ECs and Inhibitors--will stay the same.

    6. Not sure what you mean here, but maybe this will help. The software we use creates a new workbook each time we export (unfortunately I don't see a way within that app to specify what template it uses or this project might be a lot easier). Although the number of SGE compounds changes every time, the software is always analyzing 8 Inhibitors and 6 ECs.

    Thanks again. I have a feeling this isn't all that hard to do for someone who really understand the OFFSET function. Unfortunately that's not me (yet).

    Beej

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    BeeJ
    Thanks for your clear replies. For #6, It sounds like the downloaded data will only effect the size of section 2. It is much more clear to me now your intent to help you with a solution as I hope it will be for others.
    Maud

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts

    Rebuild Compounds

    BeeJ

    Place the following code in the Worksheet Activate event subroutine and it will update X column compounds from column evertime the sheet is accessed. As the SGE section grows of shrinks, it will adjust the same to the table.

    The EC and Inhibitor lists don't need to be pulled in (they are used to do the calculations within the table instead, and I have that under control).
    I am assuming that you will add the formulas and no code needed to do that.

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Activate()
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row  'FINDS THE LAST ROW
    For I = 1 To lastRow
        If Left(Cells(I, 1).Value, 3) = "SGE" Then  'IDENTIFIES AN SGE COMPOUND
            Cells(I, 24).Value = Left(Cells(I, 1).Value, 12)  'ADDS THE COMPOUND TO THE X COLUMN
            I = I + 1  'ADVANCES I BY 2 TO SKIP SGE-XXX-2 
        Else:
            If Cells(I, 1).Value = "" Then  'IF SGE SECTION SHRINKS, REMOVES FROM X COLUMN
                Cells(I, 24).Value = ""
            End If
        End If
    Next I
    End Sub
    Last edited by Maudibe; 2013-03-04 at 17:50.

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    Beej (2013-03-05)

  7. #6
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    Thanks Maudibe, that's a nice elegant solution.

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Beej

    Is there a particular reason why, on your [Processed] sheet, the formulas in columns [Y] to [AE] in, say, row 19, sometimes refer to Tier2 values (e.g. in cells [Y19], [AB19], [AD19], [AE19] ), and sometimes refer to Tier 1 values (e.g. in cells [Z19], [AA19], [AC19])?

    Just curious. I was trying to automate these formulas for when additional SGE compounds appear in the raw data.

    zeddy

  9. #8
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    Hi Zeddy,

    Yes (and I asked these lab folks the same question, btw). Some of the SGE compounds are consider "Tier 1" compounds and others are "Tier 2." It certainly made automating the formulas more complicated when I was handling that piece.

    Beej

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Beej

    OK. I understand it now.
    But in your sample file in post #1, I think there is an error in your [Report] sheet cell [C21] which displays the value 18.
    I believe the correct value should display as 36 (my computed value is 35.9543707828288).

    This is derived from your sheet [Processed] cell [Z8]
    Your formula in cell [Z8] is currently..
    =AVERAGE(V8:W9)/$W$97*100
    ..and I believe it should be..
    =AVERAGE(W8:W9)/$W$97*100


    Anyway, I have now looked at automating the formulas for your [Report] sheet.
    See attached file.

    The issue for your [Processed] sheet when any new SGE compounds are added on the [Raw - From Quant] sheet is that this will 'move' the current row 97 which contains the Average values used in the formulas.
    So my solution is to 'fix' the location of these 'Average' calcs to a new sheet [EC-Tier].
    Also, by having new sheets [SGE] and [Inhibitor], we can dispense with the sheet [Processed].

    We can then use formulas to 'retrieve' the data to sheet [Report].

    It's not quite fully automated, but a simple VBA routine could do this.

    zeddy

  11. The Following User Says Thank You to zeddy For This Useful Post:

    Beej (2013-03-19)

  12. #10
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    Hi Zeddy,

    Finally had time to follow up on this.

    You're absolutely right about the location of the averages being dependent on the number of SGE compounds. My current fix (it's a kluge) is to have my users name the cells where the averages are located before I copy in the formulas which use the averages. It's the opposite of elegant but they're OK with it.

    Having said that...I'm going to take a good, long look at the workbook you've presented to me here and see if that won't work better for them.

    THANK YOU!!!!

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Beej

    Take your time.
    Feel free to ask questions.

    Just a quick note on my post#9 file:
    On sheet [EC-Tier], I colour-coded the cells in row 3 deliberately to highlight the formula differences for Tier 1 and Tier 2 items.
    Similarly on sheets [SGE] and [Inhibitor]

    zeddy

Posting Permissions

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