Writing a Macro that looks to a value in a cell and returns a list of items that correspond to that value?

Andrea Bernard
Andrea Bernard used Ask the Experts™
on
Hi there, I'm working on writing a Macro in excel (don't have any VBA experience). I'm hoping to use the value on tab one in C5 and return the list of all items numbers that correspond to C5 (column C in tab 2). Is this possible to do with a macro?

Thank you!!
Andrea
Excel-Help-11.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please describe how you want the macro to work.

Author

Commented:
Sorry for not being clear, was hoping the Marco would return the lists of item numbers into E6 on tab one. Thank you!
AlanConsultant
Commented:
Hi,

Do you mean you want all the items into that one cell?

If so, how do you want them delimited?

Or perhaps you mean that, if there are three items (say), then you want them in E6, E7, and E8?


Thanks,

Alan.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
was hoping the Marco would return the lists of item numbers into E6 on tab one.
I understood that. What I'm asking for is an explanation of how you would like that to happen. In other words do you want to type something into C5 on tab 1 and from that have the macro fill in the list?
Got it, I was planning to insert a button to run to macro. Eventually C5 will be a drop down list of items, and then I can click the button and in the list would show up in E6 and go down, E7, E8, E9 etc.

Thanks!
Bill PrewIT / Software Engineering Consultant
Top Expert 2016
Commented:
Here's a basic solution to what you originally asked for, macro in attached.

Excel-Help-11.xlsm


»bp
AlanConsultant
Commented:
Hi,

You don't really need VBA for this.

Attached is a pure formula approach, so your file can stay VBA free (xlsx vs xlxm) plus it is self-documenting in terms of audit trail.

Just another option.


Alan.
EE-29066790-Excel-Help-11-Version1.xlsx
IT / Software Engineering Consultant
Top Expert 2016
Commented:
And a little tidier...

Excel-Help-11.xlsm


»bp
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try this.
29066790.xlsm
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...

Place the following code on Tab 1 Sheet Module. To do so, right lick the Tab 1 --> View Code --> And paste the code given below into the opened code window --> Close the VB Editor and save your workbook as Macro-Enabled Workbook.

There is a drop down list in cell C5 and once you choose a department, the relevant Item# will get populated starting from E6.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim sws As Worksheet
Dim slr As Long, lr As Long, i As Long, j As Long
Dim x, y()
If Target.Address(0, 0) = "C5" Then
    Set sws = Sheets("tab 2")
    slr = sws.Cells(Rows.Count, 2).End(xlUp).Row
    x = sws.Range("B5:C" & slr).Value
    lr = Cells(Rows.Count, 5).End(xlUp).Row
    If lr > 5 Then Range("E6:E" & lr).Clear
    If Target <> "" Then
        For i = 1 To UBound(x, 1)
            If x(i, 2) = Target.Value Then
                j = j + 1
                ReDim Preserve y(1 To j)
                y(j) = x(i, 1)
            End If
        Next i
        If j > 0 Then
            Range("E6").Resize(j).Value = Application.Transpose(y)
            Range("E6").Resize(j).NumberFormat = "0000-00#"
        End If
    End If
End If
End Sub

Open in new window

Excel-Help-11.xlsm
Rob HensonFinance Analyst

Commented:
You can achieve this without VBA or formulas. You can use a Pivot Table.

See attached. This uses the department as a Page Filter and then it shows the list of item numbers for that department.

If you were to change the data list to an excel Table rather than a standard list the Pivot would adjust automatically for the range of data expanding (or shrinking).

Thanks
Rob
Excel-Help-11.xlsx
Rob HensonFinance Analyst

Commented:
Thanks for the points. Oh wait, everybody but me received them.
AlanConsultant

Commented:
Hi Rob,

I have a the remains of a pie here, you're welcome to have.... here, catch!

Oops, sorry.  I'm sure it will come out.

Alan.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial