Excel VBA Find.Method , searching for 2 Values in a row writing a value

Question

I want to search within a table e.g.

A B C
D E C
A H I
A H C
For the Values

"A" and "C" and then put the Value "Value" into a cell in the rows where both Values have been found.
I am fairly new to the whole topic so i searched first on the web to find pieces of code that could help me.

Dim FirstAddress As String
Dim SecondAddress As String
Dim MyArr As Variant
Dim MyArr2 As Variant
Dim Rng As Range
Dim Row As Range
Dim I As Long
Dim B As Long
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
MyArr = Array("A")
MyArr2 = Array("C")
With Sheets("Sheet1").Range("F:F")
    .Offset(0, 27).ClearContents
    For I = LBound(MyArr) To UBound(MyArr)
        Set Rng = .Find(What:=MyArr(I), After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

        If Not Rng Is Nothing Then
            FirstAddress = Rng.Address
            Do
                    Set Rng2 = Rng.EntireRow.Find(What:=MyArr2(I), After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                    If Not Row Is Nothing Then
                        SecondAdress = Row.Address
                        Do
                            Rng.Offset(0, 27).Value = "Value"
                            Set Row = .FindNext(Row)

                Set Rng = .FindNext(Rng)
            Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
        End If
    Next I
End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

It worked for me searching for one value with the .find method, but i struggle to search for the rows that contain both values. (The Array in case i want to search for more than one value, say all that have at the beginning A Or D and then a C in the third column)

Do you have any idea? I dont get how i could implemented several loops.

Thanks!

Have a look here, the question is pretty similar and you should be able to adapt the solution to your needs: http://stackoverflow.com/questions/18262403/excel-vba-find-row-number-where-colum-data-multiple-clauses — tigeravatar, Sep 30 '15 at 15:11

score 1 · Answer 1 · answered Sep 30 '15 at 15:51

1

Starting with data like:

Running this macro:

Sub dural()
   Dim N As Long, i As Long, A As String, C As String, v As String
   Dim rng1 As Range, rng2 As Range, wf As WorksheetFunction
   Set wf = Application.WorksheetFunction
   A = "A"
   C = "C"
   v = "VALUE"
   N = Cells(Rows.Count, A).End(xlUp).Row
   Set rng1 = Range("A1:C" & N)

   For i = 1 To N
      Set rng2 = Intersect(Rows(i), rng1)
      If wf.CountIf(rng2, A) > 0 And wf.CountIf(rng2, C) > 0 Then
         Cells(i, "D") = v
      End If
   Next i
End Sub

will produce:

answered Sep 30 '15 at 15:51

Gary's Student

95,722
10
59
99

Hi, thanks, unfortunately it tells me here N = Cells(Rows.Count, A).End(xlUp).Row is a problem, A runtime error 1004 "Application-defined or object-defined error" – JohnDoe Oct 01 '15 at 10:56
@JohnDoe This will happen if column **A** is empty. In my example, I use column **A**, which columns are you using?? – Gary's Student Oct 01 '15 at 11:05
Hi, i actually use columns F and G , filled from F1 to G11, and adapted the code . Private Sub ToggleButton1_Click() If ToggleButton1.Value = True Then Dim num As Long, row As Long, search1 As String, search2 As String, result As String Dim rotalrng As Range, currentrng As Range, count As WorksheetFunction Set count = Application.WorksheetFunction search1 = "a" search2 = "b" result = "Yes" num = Cells(Rows.count, F).End(xlUp).row Set totalrng = Range("F1:G" & num) For row = 1 To num Set currentrng = Intersect(Rows(row), totalrng) – JohnDoe Oct 01 '15 at 15:50
If count.CountIf(currentrng, search1) > 0 And count.CountIf(currentrng, search2) > 0 Then Cells(row, "H") = result End If Next row End If End Sub – JohnDoe Oct 01 '15 at 15:55

score 0 · Answer 2 · answered Sep 30 '15 at 15:23

0

Advanced filtering would help you identify rows matching your criteria.

Data > Sort & Filter > Advanced

Microsoft Support - Filter by using advanced criteria

answered Sep 30 '15 at 15:23

Michal Schmitt

216
2
5

Excel VBA Find.Method , searching for 2 Values in a row writing a value

2 Answers2

Linked