CODEWITHSUNDEEP
vbaexcel
Null Reference
Null Reference

Reputation: 11340

Searching over multiple columns in excel vba

I am able to search a text in column A of my spreadsheet by using this

With WB.Sheets("MySheet")
    Set FindRow = .Range("A:A").Find(What:="ProjTemp1", LookIn:=xlValues)
End With

After which I can get the row number by doing FindRow.Row

How do I then get back the row number where Column A == "ProjTemp1" && Column B == "ProjTemp2" && Column C == "ProjTemp3"

Upvotes: 4

Views: 32706

Answers (4)

samuel Sai
samuel Sai

Reputation: 31

This can work in such a way that X amount of values ​​to search are Y columns to search for X values ​​in a row, having 0 as a result of nothing and Row>= 1 the row that has the X amount of values ​​per column in the same row.

Public Function find(sheetName As String, initCol As Integer, initRow As Integer, ParamArray values()) As Variant
Dim i As Long, GetRow As Long
On Error GoTo nextRow
    For i = initRow To Sheets(sheetName).cells(Rows.Count, 1).End(xlUp).row
        For ii = 0 To UBound(values)
            If Sheets(sheetName).cells(i, initCol + ii).Value2 = values(ii) Then
                    GetRow = ii
                    If ii = UBound(values) Then
                        find = i
                        Exit Function
                    End If
                    GoTo nextCol
            End If
            If ii = 0 Then GoTo nextRow
nextCol:
        Next ii
nextRow:
    Next i
endFind:
  find = GetRow
End Function

Use :

vRow = find("sheet", 1, 1, "test", "test1","test2")

"sheet" = sheetName, 1 = Col index start, 1 = row number start, ["test","test1","test2"] is ParamArray

"find" Function will search "test" in colunm A, "test1" in B & "test2" in C and it will return the row number that has these values ​​followed in the same row

Upvotes: 0

Михаил Василенко
Михаил Василенко

Reputation: 11

Just posted similar reply at MSDN and wanted to share here if anyone is still using VBA. The function for multiple match that works pretty fast.

It might help a lot if you are interested in effective code since using Application.Match() is much much faster that Find() or INDEX() method or simple looping.

The syntax is the same as COUNTIFS() but it returns the match index instead of counting.

Public Function MultiMatch(ParamArray X0() As Variant) As Variant
MultiMatch = CVErr(xlErrNA)
If UBound(X0) = -1 Then Exit Function
On Error GoTo ErrorHandler
Set Xws = X0(1).Parent
X_rFrow = X0(1)(1, 1).Row
X_rLrow = X_rFrow + X0(1).Rows.Count - 1
jLAST = UBound(X0)
l = X_rFrow

j = 0
Do While IsError(MultiMatch) And j + 1 <= jLAST And Not IsError(X1)
    jCOL = X0(j + 1).Column
    Set TRNG = Xws.Range(Xws.Cells(l, jCOL), Xws.Cells(X_rLrow, jCOL))
    X1 = Application.Match(X0(j), TRNG, 0)
    If Not IsError(X1) Then
        l = TRNG(X1).Row
        If X1 = 1 Then
            If j + 1 = jLAST Then
                MultiMatch = l - X_rFrow + 1
            Else
                j = j + 2
            End If
        Else
            j = 0
        End If
    End If
Loop
Exit Function
ErrorHandler:
MultiMatch = CVErr(xlErrName)
End Function

Upvotes: 1

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

Try to use Autofilter:

Dim rng As Range
'disable autofilter in case it's already enabled'
WB.Sheets("MySheet").AutoFilterMode = False 

With WB.Sheets("MySheet").Range("A1:C1")
    'set autofilter'
    .AutoFilter Field:=1, Criteria1:="=ProjTemp1"
    .AutoFilter Field:=2, Criteria1:="=ProjTemp2"
    .AutoFilter Field:=3, Criteria1:="=ProjTemp3"
End With

With WB.Sheets("MySheet")
    On Error Resume Next
    Set rng = .Range("A2:A" & .Rows.Count).Rows.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
End With
If Not rng Is Nothing Then
   MsgBox rng.Row ' returns first visible row number
End If

WB.Sheets("MySheet").AutoFilterMode = False 'disable autofilter'

Upvotes: 9

Netloh
Netloh

Reputation: 4378

An alternative suggestion is to just loop through the table and use nested if-statements like this:

Sub ReturnRowNumber()
    Dim i As Long, GetRow As Long
    For i = 2 To Sheets("MySheet").Cells(Rows.Count, 1).End(xlUp).Row
        'Criteria search
        If Sheets("MySheet").Cells(i, 1).Value = "ProjTemp1" Then
            If Sheets("MySheet").Cells(i, 2).Value = "ProjTemp2" Then
                If Sheets("MySheet").Cells(i, 3).Value = "ProjTemp3" Then
                    'Returns row
                    GetRow = i
                End If
            End If
        End If
    Next i
End Sub

Upvotes: 6

Related Questions