VBA searching

[kirkm]

Hi Guys,

I've got to grips with this - which finds the first occuarnce
of 2 in range A1:A500.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
End If
End With

(Thank you Bob)

However if I had two cells side by side e.g

B3 = "Hello" D3 = "Fred"

And I have "HelloFred" to search for - wanting the 3 returned (I
already know the columns), can that be acheived in a similar manner
to the above?

Many thanks - Kirk

 

[Bob Phillips]

Different approach

Dim iRow As Long
On Error Resume Next
iRow = ActiveSheet.Evaluate("Match(""HelloFred"", B1:B100&C1:C100, 0)")
On Error GoTo 0
If iRow > 0 Then
MsgBox "Found in Row " & iRow
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[kirkm]

Different approach

Dim iRow As Long
On Error Resume Next
iRow = ActiveSheet.Evaluate("Match(""HelloFred"", B1:B100&C1:C100, 0)")
On Error GoTo 0
If iRow > 0 Then
MsgBox "Found in Row " & iRow
End If

Thanks very much Bob.... peerfect!

Could I add something to specify the worksheet concerned?

Cheers - Kirk

 

[Bob Phillips]

Dim iRow As Long
With Worksheets("Sheet2") '<=== change to suit
On Error Resume Next
iRow = .Evaluate("Match(""HelloFred"",'" & .Name & "'!B1:B100&" & _
"'" & .Name & "'!C1:C100, 0)")
On Error GoTo 0
If iRow > 0 Then
MsgBox "Found in Row " & iRow
End If
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[kirkm]

Many thanks Bob, that worked spot on.

Cheers - Kirk

 

[kirkm]

Dim iRow As Long
With Worksheets("Sheet2") '<=== change to suit
On Error Resume Next
iRow = .Evaluate("Match(""HelloFred"",'" & .Name & "'!B1:B100&" & _
"'" & .Name & "'!C1:C100, 0)")
On Error GoTo 0
If iRow > 0 Then
MsgBox "Found in Row " & iRow
End If
End With

Hi Bob, Can this be modified to work it's way through the spreadsheet
so each occurance of "Hello Fred" is displayed?
The aim later would be to build an array holding this data.

Thanks - Kirk

 

[Bob Phillips]

Sub kirkm()
Const SHEET_NAME As String = "Sheet2" '<=== change to suit
Const START_ROW As Long = 1 '<=== change to suit
Const END_ROW As Long = 1000 '<=== change to suit
Dim iRow As Long
Dim iInstance As Long
Dim iStartRange As Long
Dim sRange As String

With Worksheets(SHEET_NAME)
On Error Resume Next
iStartRange = START_ROW
Do
sRange = "'" & .Name & "'!B" & iStartRange & ":B" & END_ROW &
"&" & _
"'" & .Name & "'!C" & iStartRange & ":C" & END_ROW
iRow = 0
iRow = .Evaluate("Match(""HelloFred""," & sRange & ", 0)")
If iRow > 0 Then
iInstance = iInstance + 1
MsgBox "Instance " & iInstance & " found in Row " &
iStartRange + iRow - 1
End If
iStartRange = iStartRange + iRow
Loop Until iRow = 0
On Error GoTo 0
End With

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[kirkm]

Thanks again Bob.
I added a 4th and 5th component, but gee did it slow things down.

I'm converting some Access vb stuff which uses recordsets and
findFirst etc. It's not easy !

Cheers - Kirk

 

[Bob Phillips]

Do you have loads of formulae?

If so, precede with

Application.Calculate xlCalculationManual

and reset at the end

Application.Calculate xlCalculationAutomatic

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)