+ Reply to Thread
Results 1 to 55 of 55

Use ComboBox value to reference table and dynamically output corresponding column data

Hybrid View

  1. #1
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Use ComboBox value to reference table and dynamically output corresponding column data

    I've tried a few different things but I can't seem to wrap my head around this -- Or maybe I'm just showing my just how infrequently I use excel (It's true what they say, use it or lose it -- in this case, I feel like I've lost it.)... It's disconnected in my head, but I can sorta see it -- committing anything to code, however, has proved useless thus far.

    I feel like a tool for asking, but would anyone be so kind as to provide suggestion(s) on how I might implement a combobox on sheet2 to reference column values on sheet1 and dynamically output only the corresponding adjacent column values back to sheet 2?

    I've attached an example of my data... Hopefully it makes some kind of sense.

    Template example.xlsm

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    Are your Menu Items static?

    Menus.jpg
    Menus1.jpg
    Last edited by jaslake; 02-26-2013 at 07:41 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hey, thanks for the reply, jaslake -- Yes, the row menu names will remain static... The columns will remain static for the most part, too. Only the grid data may/will change... If anything else changes, it shouldn't be much of a problem to adapt, I've just not had any success with my previous attempts -- The grid I attached previously is much smaller than the data set I'm trying to sort out, which represents about 35 columns and 500 rows between 15 servers.

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    The Column Headings changing will not be an issue...easily handled. The Grid Data, I believe, can/will be handled by Named Ranges (which may well be added with Code)...don't know as yet...we'll see...

    I'll play with it...not tonight...have Grandson duty in the AM. If no answer by tomorrow I'll work on it.

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    Another question that comes to mind...will there EVER be duplicates in Column C of Sheet1? Not a deal breaker...makes things much simpler if not.

  6. #6
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Just to clarify, I'm trying to get the data on Sheet 2 to be dynamic, comprised of data from Columns A, B & C, eliminating the grid data and condensing it so that the only thing that remains is the Server Name (column A), Menu Item Number (Column B) and Menu Item Name(column C). I'm open to alternatives with how the grid data is stored if it will make coding easier, but I have yet to think of any other way that makes sense. I suppose the Menu Item Number and Menu Item Name could be merged/concatenated to reduce the number of columns that need copied, but if Server Name and Menu Name can be done, I figured a third column could probably be added easily.

    @mehmetcik
    I had considered using the listbox approach you implemented -- it works if the menu numbers and names remain static on the second sheet, however I'm actually looking for the data being displayed under the list box to update with each selection in the combobox and only the relevant data should be returned, so if no value exists, then that row shouldn't be shown...

    Also, as far as the list entries being loaded horizontally, I had played with some VBA to load the box, using

    Private Sub ComboBox1_Change()
    ComboBox1.List = Application.WorksheetFunction.Transpose(Range("Sheet1!D2:O2"))
    Application.Calculate
    End Sub
    but I wasn't able to get it to populate successfully...

    @jaslake

    will there EVER be duplicates in Column C of Sheet1?
    Good question... I hadn't actually considered it before you said anything, but yes, there is a very good possibility of that occurring... A few of the menu options are in the same numerical position across multiple servers, and some have duplicates...


    Thanks, again, for taking the time...
    Last edited by numbnuts; 02-26-2013 at 10:16 PM.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    Ahhhh...I think I've got it...Sheet2 IS the output...

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    See if the Code in the attached does as you require. Let me know of issues.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Just out of curiosity, was the addition of the 'Data' sheet just a redundancy to ensure that sheet1's data remain untouched?

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    This is correct
    was the addition of the 'Data' sheet just a redundancy to ensure that sheet1's data remain untouched
    As you can see, Data Sheet is modified...didn't want to modify your original Sheet1.

  11. #11
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    It looks like the combobox needs the entries to be in a column not in a row

    I have modified your template for you
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    WOW! You made short work of my little brainbuster, jaslake!

    I haven't yet applied it to the full-size dataset, but it looks like your code might do the trick!!! I'll study the code and try appending it to my sheet tonight and let you know how it works out.

    Thank you for your expertise!!!

  13. #13
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Looks great! I'll look over your amendments and fixes if I have the time tonight at work...

    Thanks again, Jaslake!

  14. #14
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hey Jaslake!

    I finally got to copying the modules over to the data set I'm using and ran into a problem -- I linked the data on sheets1, 2 and 3 to read from the original workbook and modified my linked sheets to produce the same layout in our latest template example. Each sheet has all the cells populated with

    =IF('http://linkURL.domain.com/[original data set.xls]Server 1!A1 <> "", 'http://linkURL.domain.com[original data set.xls]Server 1!A1, "")
    I haven't had alot of time to review the code, but I'm wondering if the cell references in the linked data on sheets1, 2 and 3 might be instead, reading formulas instead of values to the output to the "Data" sheet. I know the output in linked data is generally "0", but when telling it to not print anything if the value is greater or less than "0" -- seems like it might be copying some cell information to the "Data" sheet, even though the information on "Data" looks fine. The output on "Data 1" appears to be consistent and as far as I can tell correct, but I'm getting offset values again on the final output and the menus are being offset into the Menu Number's expected cells, and all of the menu numbers are congregating in Column "K".

    Just wondering if you might have any thoughts or ideas on why this might occur... If I get the chance, I'll throw together an example table to see if I can duplicate the problem and post for your review...

    Thanks again!
    Last edited by numbnuts; 03-24-2013 at 02:03 AM. Reason: grammatical corrections

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    The best I can recommend is stepping through the Code to pin down where the misalignment is happening and why.

  16. #16
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Took me a while, but I think it was the fact that I had overlooked Find("Server*", ) when I linked the original spreadsheet contents into the workbook that was causing my problem, compounded by the fact that the original sheet's format wasn't "exactly" the same as what we'd been working with.

    I did, however, introduce a bit of latency that I'm not sure how to address with my recent changes and I was wondering if you might take a look and let me know if you have any suggestions... I'm at a significant disadvantage in that I'm not at all versed in any of the debugging tools inbuilt to VBA. I've tried using them, but working with them without much experience, I liken to wearing a blindfold in a dark room without walls and legos scattered about the floor.

    Not sure why the filesize is almost a MB -- maybe the file links?
    Attached Files Attached Files

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    What does this mean
    I did, however, introduce a bit of latency
    Your File Size, I'm confident, is caused by the huge number of Formulas (Links) you've introduced into the Workbook. The slowness of the Procedures is also caused by this. You might try turning Calculation Off at the Start of the Code and back On at the end of the Code like this
    Sub test()
        Dim LR As Long
        Dim cel As Range
        Dim RowOffset As Long    'Row Offset
        Dim ColumnOffset As Long    'Column Offset
        Dim k As Long
        Dim ws As Worksheet
        Dim ws1 As Worksheet
        Dim ServerAddr() As Variant
        Dim myMenu As String
        Dim myServer As String
        Dim Aarea As Range
        Dim Arange As Range
        Dim xlCalc As XlCalculation
    
        On Error GoTo ExitPoint
        With Application
            xlCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        '    Application.ScreenUpdating = False
        ReDim ServerAddr(0)
    
        Set ws = Sheets("Data1")
        Set ws1 = Sheets("Sheet4")
        ws1.Cells.Clear
    
        With ws
            .Activate
            LR = .Cells.Find("*", .Cells(rows.Count, Columns.Count), SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row
    
            For Each cel In .Range("A3:A" & LR).SpecialCells(xlCellTypeConstants)
                ServerAddr(UBound(ServerAddr)) = cel.Address
                ReDim Preserve ServerAddr(UBound(ServerAddr) + 1)
            Next cel
            ReDim Preserve ServerAddr(UBound(ServerAddr) - 1)
        End With
        RowOffset = 0
        ColumnOffset = 0
    
        For k = LBound(ServerAddr) To UBound(ServerAddr)
            ws1.Range("A4").Offset(RowOffset, ColumnOffset) = ws.Range(ServerAddr(k)).Value
            ws1.Range("A3").Offset(RowOffset, ColumnOffset) = "X"
            ws1.Range("A3").Offset(RowOffset, ColumnOffset).Font.ThemeColor = xlThemeColorDark1
            If Not k = UBound(ServerAddr) Then
                Set Arange = ws.Range("C" & Split(ServerAddr(k), "$")(2) + 2, ws.Range("D" & Split(ServerAddr(k + 1), "$")(2)).Offset(-2, 0))
                Arange.Select
                With Selection
                    myMenu = .Cells(1).Offset(-1, -1).Value
                    myServer = .Cells(1).Offset(-2, -2).Value
                End With
            Else
                Set Arange = ws.Range("C" & Split(ServerAddr(k), "$")(2) + 2, ws.Range("D" & LR))
                Arange.Select
                With Selection
                    myMenu = .Cells(1).Offset(-1, -1).Value
                    myServer = .Cells(1).Offset(-2, -2).Value
                End With
            End If
            For Each Aarea In Arange.SpecialCells(xlCellTypeConstants).Areas
                With Aarea
                    .Copy
                    '                Aarea.Select
                    ws1.Range("A1").Offset(RowOffset + 5, ColumnOffset).PasteSpecial
    
                    ColumnOffset = ColumnOffset + 2
                End With
            Next Aarea
            RowOffset = RowOffset + AddRows
            ColumnOffset = 0
        Next k
        Call Finish_Up
    ExitPoint:
        With Application
            .Calculation = xlCalc
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        '    Application.ScreenUpdating = True
    End Sub
    Beyond this, I don't know what to tell you...

  18. #18
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    I rebuilt the workbook in a fresh workbook and it reduced the filesize by another 300K or so... Once I ran the code, filesize increased to what it is now... Really, filesize isn't a huge concern -- I realize anything I add to it is only going to increase the size and or processing time and it's probably the linked data and formulas contributing to the size when compared to your last revision.

    I found that using linked data was adding 0 values to the linked data -- I'm not sure if that had any overall impact on the misalignment I was experiencing... I finally got everything working with the below code, but then in playing with the sheet realized that the 0 values might not be doing anything to the output at all, so I tried commenting out the following code in Module 2.Prep_Data

            'With ws
            'Set rng1 = ws.Cells
            
            'LR = .Cells.Find("*", .Cells(.rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row + 1
            'LC = .Cells.Find(What:="*", After:=[A1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
            
            '.Range(.Cells(1, 1), .Cells(LR, LC)).Select
            '    For Each cell In Selection
            '        If cell.Value = "" Then
            '            cell.ClearContents
            '        End If
            '    Next
            'End With
    but found when commented out would result in Module 3.Finish_Up

                    For i = 1 To LC Step 2
                        .Cells(cel.Row + 1, i + 1).Value = Split(.Cells(cel.Row + 2, i + 1), ",")(0) _
                                & "/" & Split(.Cells(cel.Row + 2, i + 1), ",")(1)
    erroring out at .Cells with 'Subscript out of range'. It works when it's un-commented, so I'm not sure exactly what it is that's not within "range" when the code isn't used since it's just an additional With expression...

    Anyway, the latency's not a huge concern either -- more of a curiosity as to whether or not there would be a better way of addressing the calculation speed or error or both. I'm fine with everything in it's current state. In any event, I'm just soooo happy to be in a somewhat completed, if not just a "working" state with this little project.

    I've attached, what should be, the final revision (with the exception of some additional aesthetic modifications) -- at some point I may come back to it and clean it up further, but now since everything just works at this point I'm going to leave it alone. Thank you again, Jaslake, for your patience and excellent assistance!

    *EDIT*

    I just refreshed the page and noticed that you had replied... I'll take a look at your suggestion and let you know -- in the meantime, I'm marking this as solved since it's, more or less, complete and I'm happy with the overall result. Thanks!
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    The reason I linked the data is because the original dataset being linked resides on a sharepoint server, which, if you're not familiar, requires a check-out/check-in process for accessing documents... To reduce the potential for problematic behavior, accessing the external data in this way seemed to be the simplest solution.

  20. #20
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Morning, Jaslake... I noticed something tonight when trying to update the links to the master workbook -- the menu names on sheet 4 aren't being pulled from Data1 as I had originally thought -- They're actually being created on the fly by a bit of code in the 'Finish_Up' sub:

                    For i = 1 To LC Step 2
                        '.Cells(cel.Row + 1, i + 1).Value = Split(.Cells(cel.Row + 2, i + 1), ",")(0) _
                                & "/" & Split(.Cells(cel.Row + 2, i + 1), ",")(1)
    I've spent a couple of hours off and on again trying to determine where I might be able modify your code to achieve my desired result, but I haven't been able to figure it out... It "looks like" the 'test' sub might be copying and pasting the server name value to Sheet4, but I can't figure where to integrate the menu names into the code -- Thinking about it, that approach might not work since it's there's more menu names than there are servers, which leaves it to the Split Cell "C"/"D" data and somehow integrating an offset with those (if that's even possible), maybe into the existing Arange(?). I know I've probably worn you out with this and I apologize for reviving this admittedly completed thread, but could you shed some light on how I might fix this? Without the menu names, the output, unfortunately, isn't of much use...

    Apologies and thanks...

  21. #21
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    What seems to be the issue? What's broken? What are you trying to fix?

  22. #22
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    It seems that Column B on Data1 isn't being used in Module 3's 'test' sub and the menu names aren't being copied to the output on Sheet4. The menu names in the current output appear to be being created or possibly overwritten by the code in my last comment... I've tried commenting out those lines of code in my worksheet and the data is looks like it's not being copied at all -- with those lines commented out, the resulting output on Sheet4 lists the server name and the data from columns C and D in Data1, without menu names.

  23. #23
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Not sure if my previous reply made any sense -- to further clarify, the menu labels that are offset 1 row below each server name on Sheet4 appear to be generated by concatenating the first line of each of column D's value in the previous set value (Module2.test.Arange) pasted to Sheet4 -- the final value being something like ColumnD.value"&/&ColumnD."value" instead of ColumnB.value.

    Below is where it looks like where the copy "should" be happening with the amended code in Module2.test you've provided...

    For Each Aarea In Arange.SpecialCells(xlCellTypeConstants).Areas
                With Aarea
                    .Copy
    I'm "slowly" putting it all together in my head -- I guess, at this point, I'm just not clear on how to integrate Data1.ColumnB's value into your code, that is, provided my observations are correct -- I've tried a couple of ways, and nothings worked yet.

  24. #24
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numnuts

    I'm not understanding the issue. I believe you're telling me your not getting the output you want/expect. I'm working with the File and Code from your Post #34. Is this the same file you're working with?

    In this file, when I select Job Title 8 the output I get is as in the attached. Please show me what you want/expect.

    By the way, I discovered what's causing the "time waster" in running the Code...perhaps you can tell me, in Prep Data, the purpose of these lines of Code
    .Range(.Cells(1, 1), .Cells(LR, LC)).Select
                For Each cell In Selection
                    If cell.Value = "" Then
                        cell.ClearContents
                    End If
                Next
    Attached Files Attached Files

  25. #25
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numbnuts

    I've added 4 lines of Code to Finish_Up...see if it works consistently for you
    Sub Finish_Up()
    
        Dim ws As Worksheet
        Dim LC As Long
        Dim cel As Range
        Dim firstAddress As String
        Dim i As Long
        Dim r As Range
        Dim rows As Long
    
        Set ws = Sheets("Sheet4")
        ws.Activate
    
        With ws.Columns(1)
            Set cel = .Find("Server*", LookIn:=xlValues)
            If Not cel Is Nothing Then
                firstAddress = cel.Address
                Do
    
                    LC = Cells(cel.Row + 2, Columns.Count).End(xlToLeft).Column
    
                    If Not LC = 1 Then                                                             'Added this line 5/29/2013
                        .Range(.Cells(cel.Row, cel.Column), .Cells(cel.Row, LC)).HorizontalAlignment = xlCenterAcrossSelection
    
                        With .Range(.Cells(cel.Row, cel.Column), .Cells(cel.Row, LC)).Font
                            .Name = "Arial"
                            .FontStyle = "Bold"
                            .Size = 8
                        End With
    
                        With .Range(.Cells(cel.Row, cel.Column), .Cells(cel.Row, LC)).Borders
                            .LineStyle = xlContinuous
                            .Weight = xlThin
                            .ColorIndex = xlAutomatic
                        End With
    
                        For i = 1 To LC Step 2
    
                            '##############################################<--------Code Removed
    
                            '                    .Cells(cel.Row + 1, i + 1).Value = Split(.Cells(cel.Row + 2, i + 1), " ")(0) _
                                                 '                            & Split(.Cells(cel.Row + 2, i + 1), ",")(1)
    
                            '##############################################<--------Code Removed
    
    
                            With .Range(.Cells(cel.Row + 1, i), .Cells(cel.Row + 1, i + 1))
                                .HorizontalAlignment = xlCenter
                                .MergeCells = True
                            End With
    
                            With .Range(.Cells(cel.Row + 1, i), .Cells(cel.Row + 1, i + 1)).Font
                                .Name = "Arial"
                                .FontStyle = "Bold"
                                .Size = 8
                            End With
    
                            With .Range(.Cells(cel.Row + 1, i), .Cells(cel.Row + 1, i + 1)).Borders
                                .LineStyle = xlContinuous
                                .Weight = xlThin
                                .ColorIndex = xlAutomatic
                            End With
                        Next i
    
                    Else                                                                             'Added this line 5/29/2013
                        .Cells(cel.Row, 1).ClearContents                                             'Added this line 5/29/2013
                    End If                                                                           'Added this line 5/29/2013
    
                    Set cel = .FindNext(cel)
                Loop While Not cel Is Nothing And cel.Address <> firstAddress
            End If
    
            .Range("a1:p1").EntireColumn.AutoFit
            .Cells.RowHeight = 15
            .Range("a1,c1,e1,g1,i1,k1,m1,o1").EntireColumn.ColumnWidth = 2.5
            .Range("a1:d1").MergeCells = True
            With .Range("a1")
                .Font.Bold = True
                .Font.Name = "Arial"
                .Font.Size = 8
                .Value = "    Please select a Job Title:"
            End With
            .Range("a2:d2").MergeCells = True
            .Range("a2").Select
        End With
    
        With ws
            Set r = .UsedRange.Offset(4, 0)
            rows = r.rows.Count
            For i = rows To 1 Step (-1)
                If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
            Next
        End With
    
    End Sub

  26. #26
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    I had added that code when I was testing to determine if the cell blanks (either "" or 0 values) were being read as values and throwing off the calculation and causing my aformentioned misalignment, I just hadn't removed it yet...

    Notes in the attachment...
    Attached Files Attached Files

  27. #27
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numnuts

    See attached...
    Attached Files Attached Files

  28. #28
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Data updated and comments added
    Attached Files Attached Files

  29. #29
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numnuts

    Try the Code in the attached
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    So I took a look at the change(s) you provided -- I get the feeling I've either ticked you off or you're having a funny at my expense. lol

    If the latter is true, then, good one -- I got a good laugh from it (For a few moments, I was "almost" convinced that a comma was the only thing that was preventing the code from including the needed data from Column B) If the former is true -- I sincerely apologize, that was never my intent. If I'm wrong altogether and you misunderstood my last explanation, I've provided yet another file that might clarify things further...

    In any case, thanks for your time and your help.
    Attached Files Attached Files

  31. #31
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numnuts

    I'm neither "ticked" off nor having "funny" at your expense. The Code appears to give the Output desired in your Post #43. It's not as quick as the Code I posted, but then, you didn't make the other change I suggested.

    So, is there a question here?
    If I'm wrong altogether and you misunderstood my last explanation, I've provided yet another file that might clarify things further...
    Last edited by jaslake; 04-07-2013 at 08:19 PM.

  32. #32
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Your code is reading from the first line of the relative output values on sheet 4 (Columns C and D of Sheet Data1), and not from the values in Sheet Data1.ColumnB -- Your code manages to achieve the result of matching the text value and/or formatting convention I mentioned in comment 43, but if values in the code below are modified to look for elements in the values of Sheet Data1.ColumnB, or if the values in Data1.ColumnB are modifed, because those values haven't been copied and do not exist, the code bombs out.

    .Cells(cel.Row + 1, i + 1).Value = Split(.Cells(cel.Row + 2, i + 1), " ")(0) _  '<- note the " ", and the "," values on the output of Sheet4
                & Split(.Cells(cel.Row + 2, i + 1), ",")(1)                                       '    do not conform to the data in Column B of Sheet ("Data1")...
    The above code isn't necessary if the values are copied from Sheet Data1.ColumnB somewhere in the 'test' sub. I'm trying to understand how to add that data into the values that are being copied from Sheet Data1.ColumnB to the final output on Sheet4, but it's unclear to me how to do this...
    Last edited by numbnuts; 04-08-2013 at 12:10 AM.

  33. #33
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numnuts

    The Code in the attached has been modified to pull values
    from the values in Sheet Data1.ColumnB
    The following changes are identified in the Code:
    In Sub Prep_Data()
    '##############################################<--------Code Changed
            .Range(.Cells(1, 1), .Cells(LR, LC)).SpecialCells(xlCellTypeVisible).Select
    '##############################################<--------Code Changed
    In Sub test()
    '##############################################<--------Code Added
                    For i = 1 To 1
                        ws1.Range("B1").Offset(RowOffset + 4, ColumnOffset) = Aarea(i).Offset(-1, -1)
                    Next i
    '##############################################<--------Code Added
    In Sub Finish_Up()
    '##############################################<--------Code Removed
                    
    '                    .Cells(cel.Row + 1, i + 1).Value = Split(.Cells(cel.Row + 2, i + 1), " ")(0) _
    '                            & Split(.Cells(cel.Row + 2, i + 1), ",")(1)
    
    '##############################################<--------Code Removed
    The attached jpg demonstrates my output for Job Title 8.
    Attached Images Attached Images
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Thank you for this, Jaslake... This seems to have done the trick!

      For i = 1 To 1
        ws1.Range("B1").Offset(RowOffset + 4, ColumnOffset) = Aarea(i).Offset(-1, -1)
      Next i
    I kept looking at the line above it

    ws1.Range("A1").Offset(RowOffset + 5, ColumnOffset).PasteSpecial
    but the (RowOffset + 5, ColumnOffset) didn't make sense to me. It still kinda doesn't since +5, in my mind, would imply offsetting +5 rows -- given this, your addition after that (RowOffset +4, ColumnOffset), then, seems counter-intuitive with respect to placement of the output with the pastespecial directive... I'm sure I'll figure it out, eventually...

    Thanks for your patience with me, Jaslake. I haven't had alot of time to spend towards learning and working with the debugging tools (I didn't assign myself the numbnuts handle without good reason. I hope that was apparent...) -- I've learned a little about it over the course of our correspondence and to be sure, with each iteration, I've learned something new about VBA -- One of these days (hopefully sooner, rather than later), I'll be able to dedicate some time to learning how to properly use them -- speaking of which, do you have any recommendations for third party open training resources for debugging VBA, aside from the usual suspects (MSDN, Ozgrid, etc)?

    Thanks again for your time and instruction -- I'll continue to apply what I've learned from you for many years, I'm sure.

  35. #35
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numnuts

    That particular Code snippet is ALWAYS working off of Cell A1 of Sheet4
    Set ws1 = Sheets("Sheet4")
    Row Offset is initially set to 0
    RowOffset = 0
    Then it's set to
    RowOffset = RowOffset + AddRows
    and AddRows is a Constant
    Public Const AddRows As Long = 25
    Step through the Code...you'll see it...

  36. #36
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hey Jaslake!

    I wonder if I might ask your assistance one more time -- I've not been able to come back to this until recently having been ill or otherwise busy -- I've been using it somewhat regularly but I've decided to clean up one last remaining problem.

    I discovered shortly after our last correspondence that adding any servers where that server's output may not apply to the dropbox selection results in some data being carried over from the clipboard. Having dealt with that particular problem, I'm left with a server name that takes the cell formatting in the finish_up sub and subsequently put's the text partially off screen... Ultimately, I'd prefer to clear that server name's cell if no data exists for that server, but I'm perplexed as to how to accomplish it. I've tried several different ways to either clear or delete it using either the test or finish_up subs, but end up clearing or deleting the first cel.address for all of them or not affecting any data at all.

    If you have a moment, and don't mind, could you take a look and offer your thoughts/insight?
    Attached Files Attached Files

  37. #37
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Hi numnuts

    Can you give me a for instance so I can attempt to duplicate the issue?

  38. #38
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    In it's current "saved" state, Server 2's out put on sheet four is non-existent. I added some error trapping in the 'test' sub to prevent it dropping the previous paste value

        
    If Aarea Is Nothing Then Application.CutCopyMode = False
        On Error Resume Next
    When I added the On Error Resume Next statement, it would paste the last value from Server 1 into Server 2. If you remove the first line of the above code, you'll kinda see what I mean... In stopping it pasting that value, the value 'Server 2' on sheet 4 is formatted by the 'finish_up' sub and subsequently ugly to the resulting output for the entire page and not even necessary. What I had tried didn't work in removing it:

            With ws.Columns(1)
            Set cel = .Find("Server*", LookIn:=xlValues)
            If Not cel Is Nothing Then
                firstAddress = cel.Address
                Do
                    If cel.Offset(1, 1) = 0 Then cel.Delete
                                Set cel = .FindNext(cel)
                Loop While Not cel Is Nothing And cel.Address <> firstAddress
                End If
            End With
    I tried placing the above before the last 'With' statement in the 'finish_up' sub (apologies, that's the only iteration of my attempts that I've saved -- I've tried various 'If/Then' one liners in various places throughout both the 'test' and 'finish_up' subs) but it blows away the 'Server 1' field and subsequently throws off the formatting of the entire sheet. In the above, I figured since there's data in next row/next column of all the other fields I wanted to keep, it would ignore those cells cell and loop until it found one where that matched the criteria; That's what most of my attempts had been generally based on -- I didn't want to blow away your "X" placeholder, so I used the cell next to it for reference in most of my attempts, but with disastrous results most of the time...

  39. #39
    Registered User
    Join Date
    07-02-2012
    Location
    614
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Use ComboBox value to reference table and dynamically output corresponding column data

    Jaslake,

    Based on preliminary testing, your additions appear to do what I need them to; I'll merge them into my data later tonight when I'm at work to confirm...

    Thank you, yet again, for your excellent assistance!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1