Log In
Register
Home
New Member
March 25, 2022
Offline
Hello,
I’m new to excel VBA and I’m trying to write a macro that uses a user supplied input to locate values in a middle table, then use the values located to sift the other two tables shown on the data sheet. The issue I seem to have is locating the middle table row number using the supplied input, and I’ve been stuck on making my next steps.
If there’s more information you need, let me know.
Trusted Members
Moderators
November 1, 2018
Offline
You could use something like this:
Sub Searchable_List()
'Execute search for nestable products
Dim CalculatorDraft As Worksheet
Dim PreviousProduct As ListObject
Dim CompareTo As ListObject
Dim NextProduct As ListObject
Dim RowNum As Range
'Assign sheet and tables
Set CalculatorDraft = Sheets("CalculatorDraft")
Set PreviousProduct = CalculatorDraft.ListObjects("PreviousProduct")
Set CompareTo = CalculatorDraft.ListObjects("CompareTo")
Set NextProduct = CalculatorDraft.ListObjects("NextProduct")
'Assign input
nestinput = CalculatorDraft.Range("J4").Value
'Check if there is any user input.
If nestinput <> "" Then
'User input a value, so proceed
'Clear any filters
PreviousProduct.AutoFilter.ShowAllData
CompareTo.AutoFilter.ShowAllData
NextProduct.AutoFilter.ShowAllData
'Apply new filters, filter CompareTo table to find product
Dim RowPos
RowPos = Application.Match(nestinput, CompareTo.DataBodyRange.Columns(1), 0)
If Not IsError(RowPos) Then
'Assign value for filtering
With CompareTo
Technology = .ListColumns("Technology").DataBodyRange.Cells(RowPos).Value
Colors = .ListColumns("Color").DataBodyRange.Cells(RowPos).Value
Filler = .ListColumns("Filler").DataBodyRange.Cells(RowPos).Value
Thixotropy = .ListColumns("Thixotropy").DataBodyRange.Cells(RowPos).Value
Compliance = .ListColumns("Compliance").DataBodyRange.Cells(RowPos).Value
End With
'Filtering sequence for previous product
'Filter for Technology Type
With PreviousProduct.Range
.AutoFilter Field:=2, Criteria1:=Technology
'Filter for Color
.AutoFilter Field:=3, Criteria1:="<" & Colors
'Filter for Filler
.AutoFilter Field:=4, Criteria1:="<" & Filler
'Filter for Thixotropy
.AutoFilter Field:=5, Criteria1:="<" & Thixotropy
'Filter for Compliance
.AutoFilter Field:=6, Criteria1:="<" & Compliance
End With
'Filtering sequence for NextProduct Table
'Filter for Technology
With NextProduct.Range
.AutoFilter Field:=2, Criteria1:=Technology
'Added end if to stop sequence
'Filter for Color
.AutoFilter Field:=3, Criteria1:=">" & Colors
'Filter for Filler
.AutoFilter Field:=4, Criteria1:=">" & Filler
'Filter for Thixotropy
.AutoFilter Field:=5, Criteria1:=">" & Thixotropy
'Filter for Compliance
.AutoFilter Field:=6, Criteria1:=">" & Compliance
End With
Else
'Nothing exists in table
MsgBox "Product not in matrix, verify correct or have product added"
End If
Else
MsgBox " Enter product name "
End If
End Sub
