I'm trying to search a worksheet for a row where the values in the first 3 columns match a set of 3 criteria. I'm using this linear search:

Function findRow(pName as string,fNo as string,mType as string) As Long Dim rowCtr As Long rowCtr = 2 While Not rowMatchesCriteria(rowCtr, pName,fNo,mType) rowCtr = rowCtr + 1 Wend findRow=rowCtr End Function Function rowMatchesCriteria(row As Long, pName As String, fNo As String, mType As String) As Boolean rowMatchesCriteria = dSheet.Cells(row,1)=pName _ And dSheet.Cells(row,2)=fNo _ And dSheet.Cells(row,3)=mType End Function 

We can assume that for any 3 criteria, there is only one match. However, this is very slow. dSheet has ~35,000 entries to search through, and I need to perform ~400,000 searches.

I looked at some of the solutions in this question, and while I'm sure that using AutoFilter or an advanced would be faster than a linear search, I don't understand how to get the index of the row that the filter returns. What I'm looking for would be:

Sub makeUpdate(c1 as string,c2 as string,c3 as string) Dim result as long result = findRow(c1,c2,c3) dSheet.Cells(result,updateColumn) = someUpdateValue End Sub 

How do I actually return the result row that I'm looking for once I've applied AutoFilter?

I'm trying to search a worksheet for a row where the values in the first 3 columns match a set of 3 criteria. I'm using this linear search:

Function findRow(pName as string,fNo as string,mType as string) As Long Dim rowCtr As Long rowCtr = 2 While Not rowMatchesCriteria(rowCtr, pName,fNo,mType) rowCtr = rowCtr + 1 Wend findRow=rowCtr End Function Function rowMatchesCriteria(row As Long, pName As String, fNo As String, mType As String) As Boolean rowMatchesCriteria = dSheet.Cells(row,1)=pName _ And dSheet.Cells(row,2)=fNo _ And dSheet.Cells(row,3)=mType End Function 

We can assume that for any 3 criteria, there is only one match. However, this is very slow. dSheet has ~35,000 entries to search through, and I need to perform ~400,000 searches.

I looked at some of the solutions in this question, and while I'm sure that using AutoFilter or an advanced would be faster than a linear search, I don't understand how to get the index of the row that the filter returns. What I'm looking for would be:

Sub makeUpdate(c1 as string,c2 as string,c3 as string) Dim result as long result = findRow(c1,c2,c3) dSheet.Cells(result,updateColumn) = someUpdateValue End Sub 

How do I actually return the result row that I'm looking for once I've applied AutoFilter?

I'm trying to search a worksheet for a row where the values in the first 3 columns match a set of 3 criteria. I'm using this linear search:

Function findRow(pName as string,fNo as string,mType as string) As Long Dim rowCtr As Long rowCtr = 2 While Not rowMatchesCriteria(rowCtr, pName,fNo,mType) rowCtr = rowCtr + 1 Wend findRow=rowCtr End Function Function rowMatchesCriteria(row As Long, pName As String, fNo As String, mType As String) As Boolean rowMatchesCriteria = dSheet.Cells(row,1)=pName _ And dSheet.Cells(row,2)=fNo _ And dSheet.Cells(row,3)=mType End Function 

We can assume that for any 3 criteria, there is only one match. However, this is very slow. dSheet has ~13,000 entries to search through, and I need to perform ~400,000 searches.

I looked at some of the solutions in this question, and while I'm sure that using AutoFilter or an advanced would be faster than a linear search, I don't understand how to get the index of the row that the filter returns. What I'm looking for would be:

Sub makeUpdate(c1 as string,c2 as string,c3 as string) Dim result as long result = findRow(c1,c2,c3) dSheet.Cells(result,updateColumn) = someUpdateValue End Sub 

How do I actually return the result row that I'm looking for once I've applied AutoFilter?

I'm trying to search a worksheet for a row where the values in the first 3 columns match a set of 3 criteria. I'm using this linear search:

Function findRow(pName as string,fNo as string,mType as string) As Long Dim rowCtr As Long rowCtr = 2 While Not rowMatchesCriteria(rowCtr, pName,fNo,mType) rowCtr = rowCtr + 1 Wend findRow=rowCtr End Function Function rowMatchesCriteria(row As Long, pName As String, fNo As String, mType As String) As Boolean rowMatchesCriteria = dSheet.Cells(row,1)=pName _ And dSheet.Cells(row,2)=fNo _ And dSheet.Cells(row,3)=mType End Function 

We can assume that for any 3 criteria, there is only one match. However, this is very slow. dSheet has ~35,000 entries to search through, and I need to perform ~400,000 searches.

I looked at some of the solutions in this question, and while I'm sure that using AutoFilter or an advanced would be faster than a linear search, I don't understand how to get the index of the row that the filter returns. What I'm looking for would be:

Sub makeUpdate(c1 as string,c2 as string,c3 as string) Dim result as long result = findRow(c1,c2,c3) dSheet.Cells(result,updateColumn) = someUpdateValue End Sub 

How do I actually return the result row that I'm looking for once I've applied AutoFilter?