CODEWITHSUNDEEP
excelvba
Tara
Tara

Reputation: 15

Excel VBA search two different columns for a result to then paste

I have a search worksheet that I use to search a different worksheet. The macro works but I am unable to search two columns for the same word. I tired OR instead of And and that didnt work. I also tried adding another integer but that didnt work either. Here is a little from my macro... basically i want to search column 6 and 7 for the same word.

Sub Searchcustomer()


Dim audit As String
Dim saudit As String
Dim finalrow As Long
Dim i As Long




Set msheet = Sheet11
Set ssheet = Sheet10


audit = ssheet.Range("B8").Value




msheet.Select
finalrow = msheet.Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To finalrow
    If  IIf(audit <> "", Cells(i, 6) = audit, True) Then
        msheet.Range(msheet.Cells(i, 1), msheet.Cells(i, 9)).Copy
        msheet.Range(msheet.Cells(i, 1), msheet.Cells(i, 9)).Copy Destination:=ssheet.Range("A100").End(xlUp).Offset(1, 0).Resize(1, 9)

    End If

Next i




ssheet.Select
ssheet.Range("B3").Select



End Sub

Upvotes: 0

Views: 38

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

For i = 1 To finalrow
    With msheet.rows(i)
    If  IIf(audit <> "", (.Cells(6) = audit Or .Cells(7) = audit), True) Then

       .Cells(1).Resize(1, 9).Copy _
          Destination:=ssheet.Range("A100").End(xlUp).Offset(1, 0)

    End If
    End With
Next i

Upvotes: 2

Related Questions