Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA searching
Many thanks Bob, that worked spot on.
Cheers - Kirk |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching | Excel Worksheet Functions | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
searching for tab | Excel Discussion (Misc queries) | |||
searching | Excel Worksheet Functions | |||
searching for a value... | Excel Programming |