Find Findnext in selected range
 

Hi, I am a newbie at find/findnext in selected ranges.
I have tried typing in verbatim the example of findnext code
and receive runtime error 9 Subscript out of range. I would like
to search a set range for the character string "7F", insert 2 rows
beneath, fill Cells with the chrstring "LINUX" and then find
the next "7F". I need to do this 8 times.


My original code was as follows:
Dim x, y, z as Integer
Dim rng1 as Range


Range("B:B").Find(what:="3000").Activate
y = ActiveCell.row
Range("B:B").Find(what:="3792").Activate
z = ActiveCell.row


With ActiveSheet
Set rng1 = Range(Cells(y,2),Cells(z,2))
End With


rng1.Find(what:="7F").Activate
For x = 1 to 8


Rows(z + 1 & ":" & z + 2).Insert
Range(Cells(z + 1, 4, Cells(z + 2, 4)) = "LINUX"
ActiveSheet.rng1.FindNext(after:=ActiveCell).Activ ate
z = Activecell.Row
Next x
End Sub

This failed on the FindNext line, so I researched this google group
and found similar code as in the VBA HELP Dialog box and
tested the following code to see if it would work. Thats
when I received the Subscript out of range.


With ActiveSheet.Range("e6:e28")
Set c = .Find("7F", lookin:=x1Values)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set c = .FindNext(c)
Loop While Not c is Nothing And c.Address < firstAddress
End If
End With


Neither code worked for me. Can anyone tell where I am going
wrong with the code? Much appreciated.

Find Findnext in selected range
 

Sub ABCD()
Dim x As Long, y As Long, z As Long
Dim rng1 As Range


Range("B:B").Find(what:="3000").Activate
y = ActiveCell.Row
Range("B:B").Find(what:="3792").Activate
z = ActiveCell.Row


With ActiveSheet
Set rng1 = .Range(.Cells(y, 2), .Cells(z, 2))
End With

For x = 1 To 8
rng1.Find(what:="7F").Activate
ActiveCell.Offset(1).Resize(2).EntireRow.Insert
ActiveCell.Offset(1).Resize(2).Value = "LINUX"
z = z + 2
Set rng1 = Range(ActiveCell.Offset(2), Cells(z, 2))
Next x
End Sub

worked for me if you have 8 cells containing 7F between the cells with 3000
and 3792

--
Regards,
Tom Ogilvy


"looloo" wrote in message
oups.com...
Hi, I am a newbie at find/findnext in selected ranges.
I have tried typing in verbatim the example of findnext code
and receive runtime error 9 Subscript out of range. I would like
to search a set range for the character string "7F", insert 2 rows
beneath, fill Cells with the chrstring "LINUX" and then find
the next "7F". I need to do this 8 times.


My original code was as follows:
Dim x, y, z as Integer
Dim rng1 as Range


Range("B:B").Find(what:="3000").Activate
y = ActiveCell.row
Range("B:B").Find(what:="3792").Activate
z = ActiveCell.row


With ActiveSheet
Set rng1 = Range(Cells(y,2),Cells(z,2))
End With


rng1.Find(what:="7F").Activate
For x = 1 to 8


Rows(z + 1 & ":" & z + 2).Insert
Range(Cells(z + 1, 4, Cells(z + 2, 4)) = "LINUX"
ActiveSheet.rng1.FindNext(after:=ActiveCell).Activ ate
z = Activecell.Row
Next x
End Sub

This failed on the FindNext line, so I researched this google group
and found similar code as in the VBA HELP Dialog box and
tested the following code to see if it would work. Thats
when I received the Subscript out of range.


With ActiveSheet.Range("e6:e28")
Set c = .Find("7F", lookin:=x1Values)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set c = .FindNext(c)
Loop While Not c is Nothing And c.Address < firstAddress
End If
End With


Neither code worked for me. Can anyone tell where I am going
wrong with the code? Much appreciated.

Find Findnext in selected range
 

Tom,

Thank you. The code worked beautifully and I was able to learn a few
coding techniques from your example. Kudos!

Regards,
Leslie