range.find

joneil

New Member
Joined
Sep 16, 2006
Messages
4
i am trying to create a macro to search for a specific string within a given range and then output to a different column of the same row, for example, in the following code i was attempting to search within column H for "Stage 2" and each time time it occurred, I wanted it to output "Stage 2" to column A for that row. the problem is that i keep getting runtime error 91: Object variable or With block variable not set and when i click debug the portion of code from "stage2 = Selection.Find" to ".Activate)" is highlighted. i suspect this is not that difficult of a macro but i just don't know what i'm doing

Dim Stage2 As Range
For i = 1 To 30000
Range("H" & i).Select
Stage2 = Selection.Find(What:="Stage 2", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If Stage2 <> "" Then
Range("A" & i).Formula = "Stage 2"
End If
Next i
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Set Stage2 = Selection.Find(What:="Stage 2", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not Stage2 IsNothing Then
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

How's this (eliminating the For...Next structure):

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FindStage2()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> Range([H1], Cells(Rows.Count, "H").End(xlUp))
        <SPAN style="color:#00007F">Set</SPAN> c = .Find("Stage 2", LookIn:=xlValues)
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            FirstAddress = c.Address
            <SPAN style="color:#00007F">Do</SPAN>
                c.Offset(, -7) = "Stage 2"
                <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)
            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> FirstAddress
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
joneil

Couldn't this be done with a simple formula in column A?

=IF(ISNUMBER(SEARCH( "Stage 2", H1)), "Stage 2", "")

If needed code could be written to place this formula in column A and then replace it with values using paste special.
Code:
LastRow = Range("H" & Rows.Count).End(xlUp).Row

With Range("A1").Resize(LastRow)
      .Formula = "=IF(ISNUMBER(SEARCH(""Stage 2"", H1)), ""Stage 2"", """")"
      .Copy
      .PasteSpecial xlPasteValues
End With
 

joneil

New Member
Joined
Sep 16, 2006
Messages
4
thanks a lot guys for all your help, i really appreciate it. i went with VoG II's solution only because it kept my original outline and my knowledge is so limited that that's about all i know how to work with. thanks again all
 

Forum statistics

Threads
1,141,681
Messages
5,707,792
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top