range.find

[joneil]

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

 

[VoG]

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]

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]

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.

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]

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