VBA to create list based on input

cvrband

Board Regular
Joined
Jan 6, 2016
Messages
61
Office Version
  1. 365
Platform
  1. Windows
I would like help creating VBA that creates a unique list based on input in cell 'H1' and provides vertical output starting at cell 'E5'. (cell 'H1' will always be a 1- or 2-digit number)

Starting in cell 'A5' of the sheet named "Products" I have a list of unique product numbers. Starting in cell 'C5' I have a list of product types. Because the number of products varies from project to project, the last row of both columns 'A' & 'C' needs to be determined by column 'B' ( LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row ) due to values input by user in column 'B'. There will be no blank rows in column 'B' but values in columns 'A' & 'C' could falsely extend past the last row of column 'B' and should not be included in the result search.

Request: VBA to provide a list starting at 'E5' of products (column 'A') that match product type (column 'C') and the value that the user input in cell 'H1' without blank rows between the results?

I plan to fire this routine with a button, which I am able to set up.

Thank you in advance.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Can you provide some sample data and show a before and after scenario of what you are looking for. Your explanation is difficult to follow and we need more specific information regarding products and product type to formulate any type of code.
 
Upvote 0
Firstly, do you really need a macro? This formula in E5 and copied down as far as you might ever need should give you the list.

=IFERROR(INDEX(A$5:A$1000,AGGREGATE(15,6,(ROW(A$5:A$1000)-ROW(A$5)+1)/((C$5:C$1000=H$1)*(B$5:B$1000<>"")),ROWS(E$5:E5))),"")


If you do need a macro, then try this in a copy of your workbook.

Code:
Sub GetProducts()
  With Range("A4:C" & Range("B" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=3, Criteria1:=Range("H1").Value
    If .SpecialCells(xlVisible).Count > 3 Then
      .Offset(1).Resize(.Rows.Count - 1).Columns(1).Copy Destination:=Range("E5")
    End If
    .AutoFilter Field:=3
  End With
End Sub
 
Upvote 0
Thank you Peter_SSs, your code works great! I was using an array formula similar to what you suggested and it was causing problems. I also wanted to cut out extra calculations in this large spreadsheet. Thanks again for your help!
 
Upvote 0
Thank you Peter_SSs, your code works great! I was using an array formula similar to what you suggested and it was causing problems. I also wanted to cut out extra calculations in this large spreadsheet. Thanks again for your help!
You're welcome. Thanks for the extra explanation. :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top