VBA searching
 

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

VBA searching
 

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" wrote in message
...
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

VBA searching
 

On Thu, 14 Sep 2006 11:21:44 +0100, "Bob Phillips"
wrote:

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

VBA searching
 

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" wrote in message
...
On Thu, 14 Sep 2006 11:21:44 +0100, "Bob Phillips"
wrote:

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

VBA searching
 

Many thanks Bob, that worked spot on.

Cheers - Kirk

VBA searching
 

On Thu, 14 Sep 2006 12:48:07 +0100, "Bob Phillips"
wrote:

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

VBA searching
 

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" wrote in message
...
On Thu, 14 Sep 2006 12:48:07 +0100, "Bob Phillips"
wrote:

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

VBA searching
 

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

VBA searching
 

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)

"kirkm" wrote in message
...
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