PDA

View Full Version : Returning multiple results with Index/Match



bifjamod
08-02-2016, 05:27 PM
The following script returns the first occurrence of the part number in question; because multiple accounts will have the same part number, I need to return multiple records. Can anyone offer some direction on how I might return multiple matches? My end game is a userform that displays all the results and allows me to select the desired result....but for now, I'm content just to display multiple results.


strPartNum = "SCREW"

strTariff = Application.WorksheetFunction.Index(Workbooks("DATA").Sheets("PARTS").Range("B:B"), Application.WorksheetFunction.Match(strPartNum, Workbooks("DATA").Sheets("PARTS").Range("A:A"), 0))
strCustomer = Application.WorksheetFunction.Index(Workbooks("DATA").Sheets("PARTS").Range("B:B"), Application.WorksheetFunction.Match(strPartNum, Workbooks("DATA").Sheets("PARTS").Range("A:A"), 0)).Offset(0, 4).Value

MsgBox strTariff & " " & strCustomer

SamT
08-02-2016, 06:50 PM
Dim TarrifsCutomers as Variant
Const Tarriffs as long = 1
Const Custs As Long = 2

Dim NumParts as Long
Dim Found As Range
Dim PartsColumn As Range
Dim FirstFoundAddress As String
Dim F As Long

Set PartsColumn = Workbooks("DATA").Sheets("PARTS").Range("B:B") 'or "A:A" your choice.
'You will have to adjust all ranges and offsets herein

NumParts = Application.WorksheetFunction.CountIf(PartsColumn, strPartNum)

Redim TarrifsCutomers(1 to NumParts, Tarriffs to Custs)

Set Found = PartsCoumn.Find(strPartNum)
FirstFoundAddress = Found.Address

Do
F = F + 1
TarrifsCutomers(f, Tarriffs) = Found.Offset(, -1) 'Column A From B
TarrifsCutomers(f, Custs) = Found.Offset(, 3) 'Column E From B

Set Found = FindNext(strPartNum)
Loop While Found.Address <> FirstFoundAddress

FrmMyUserForm.Controls.MyListBox.List = TarrifsCustomers

I am pretty certain that the Bailey's has had it's intended afect. If I werre you I would not trust the details of that code at all.

bifjamod
08-03-2016, 12:02 PM
Interesting approach - certainly not what I was expecting (then again, I really didn't know what to expect....)

Here's my update to the script:

Sub TestPARTSDICTIONARY()
Dim TarrifsCutomers As Variant
Const Tarriffs As Long = 1
Const Custs As Long = 2

Dim strPartNum As String

Dim NumParts As Long
Dim Found As Range
Dim PartsColumn As Range
Dim FirstFoundAddress As String
Dim F As Long

strPartNum = "SCREW" '7 SAMPLES EXIST IN WORKBOOK

Set PartsColumn = Workbooks("DATA").Sheets("PARTS").Range("A:A")

NumParts = Application.WorksheetFunction.CountIf(PartsColumn, strPartNum)

ReDim TarrifsCutomers(1 To NumParts, Tarriffs To Custs)

Set Found = PartsColumn.Find(strPartNum)
FirstFoundAddress = Found.Address

Do
F = F + 1
TarrifsCutomers(F, Tarriffs) = Found.Offset(, 1)
TarrifsCutomers(F, Custs) = Found.Offset(, 5)

Set Found = PartsColumn.FindNext(strPartNum)
Loop While Found.Address <> FirstFoundAddress

FrmMyUserForm.Controls.MyListBox.List = TarrifsCutomers

End Sub

This is producing the following error:
Run-time error 1004: Unable to get the FindNext property of the Range class.

Any suggestions? I'll poke around the interwebs later to see if that gives me any thoughts on what I need to adjust to overcome the error.

SamT
08-03-2016, 01:41 PM
Set Found = FindNext(strPartNum)

Since I sspect you will integrate this into a longer Project, I suggest you add this before ReDimming the Array

If NumParts = 0 Then
MsgBox strPartNum & "Not found"
Exit sub
End If

bifjamod
08-03-2016, 01:55 PM
Set Found = FindNext(strPartNum)
I did try this initially, but get the following:
Compile error: Sub or Function not defined.

Good idea on the escape when no match is found.

SamT
08-03-2016, 03:06 PM
It's been a while, I had to look it up

Sub t()
Dim X, Y
Set X = Range("A:A").Find("A")
Set X = Range("A:A").FindNext(After:=X)
Y = X.Address
End Sub


Alternately

Sub t()
Dim X, Y
With Range("A:A")
Set X = .Find("A")
Set X = .FindNext(After:=X)
Y = X.Address
End With
End Sub

bifjamod
08-04-2016, 08:04 AM
I'm afraid you've lost me with this one: I don't understand how to incorporate that into the prior script, or how I would initiate the search if that is the entirety of the script. Sometimes I miss the obvious....

SamT
08-04-2016, 09:50 AM
Do
F = F + 1
TarrifsCutomers(F, Tarriffs) = Found.Offset(, 1)
TarrifsCutomers(F, Custs) = Found.Offset(, 5)

Set Found = PartsColumn.FindNext '<-------------
Loop While Found.Address <> FirstFoundAddress

bifjamod
08-04-2016, 10:54 AM
I do appreciate your time. Unfortunately, I'm not understanding how to implement your solution.

SamT
08-04-2016, 11:20 AM
Read my solution then read your code and compare the two

SamT
08-04-2016, 11:22 AM
Sub TestPARTSDICTIONARY()
Dim TarrifsCutomers As Variant
Const Tarriffs As Long = 1
Const Custs As Long = 2

Dim strPartNum As String

Dim NumParts As Long
Dim Found As Range
Dim PartsColumn As Range
Dim FirstFoundAddress As String
Dim F As Long

strPartNum = "SCREW" '7 SAMPLES EXIST IN WORKBOOK

Set PartsColumn = Workbooks("DATA").Sheets("PARTS").Range("A:A")

NumParts = Application.WorksheetFunction.CountIf(PartsColumn, strPartNum)

ReDim TarrifsCutomers(1 To NumParts, Tarriffs To Custs)

Set Found = PartsColumn.Find(strPartNum)
FirstFoundAddress = Found.Address

'Do
' F = F + 1
' 'TarrifsCutomers(F, Tarriffs) = Found.Offset(, 1)
' TarrifsCutomers(F, Custs) = Found.Offset(, 5)
'
' Set Found = PartsColumn.FindNext(strPartNum)
'Loop While Found.Address <> FirstFoundAddress

Do
F = F + 1
TarrifsCutomers(F, Tarriffs) = Found.Offset(, 1)
TarrifsCutomers(F, Custs) = Found.Offset(, 5)

Set Found = PartsColumn.FindNext '<-------------
Loop While Found.Address <> FirstFoundAddress

FrmMyUserForm.Controls.MyListBox.List = TarrifsCutomers

End Sub

bifjamod
08-04-2016, 11:43 AM
That doesn't work. Thanks anyway; I'll keep searching for a solution to my need. If anyone (else) can tell me what I'm missing in SamT's solution, I'd appreciate the insight.

bifjamod
08-04-2016, 11:59 AM
I don't wish to seem ungrateful, because I'm not - it's just that my frustration is venting in a way that is leaving us both, I think, confused.

I get your solution where you reference
Set Found = PartsColumn.FindNext '<------------
However, removing "(strPartNum)" produces a run-time 424 error, object required.

Additionally, I fail to see what your earlier posts have to do with this, which is causing a significant part of my confusion:

Sub t()
Dim X, Y
With Range("A:A")
Set X = .Find("A")
Set X = .FindNext(After:=X)
Y = X.Address
End With
End Sub

SamT
08-04-2016, 12:43 PM
Thanks for reminding me. I am working with several other members on their issues and got lost

Set Found = PartsColumn.FindNext '<------------
Should be

Set Found = .FindNext(After:=Found)

My very bad and I appologise profusely.


:banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead:

bifjamod
08-04-2016, 01:05 PM
That produces the following:

Compile error: Invalid or unqualified reference.

Is this fix assuming that the below code has been inserted somewhere in this script? If so, I really need you to hold my hand and hit me in the face with what seems obvious to you (like *where* in the script it goes, because my experimentation has been fruitless), because I'm lost:

Sub t()
Dim X, Y
With Range("A:A")
Set X = .Find("A")
Set X = .FindNext(After:=X)
Y = X.Address
End With
End Sub

SamT
08-04-2016, 02:02 PM
Sub t()
Dim X, Y
With Range("A:A")
Set X = .Find("A")
Set X = .FindNext(After:=X)
Y = X.Address
End With
End Sub

That is just an example of how to use FindNext. Do not insert it in your code. It doesn't even look like it belongs in your code

From your first post it looks like you are trying to learn VBA. I am trying to teach you, but you still have to think for yourself.

If all you want is to get some code that does what you need, then you will have to provide a sample workbook and a detailed explanation of what you need. If that is the case, my responses will be different and my code will be thoroughly tested for you.

As a tutor, I rarely fully test my code because the errors provide a learning experience for students. If I do happen to develop perfected code, I will often introduce errors before I present it.

I swear, someday I'm gonna quit this job. It doesn't pay enough.
"Well, Sam. How much does it pay?"
"Nothing."