Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Findnext | Excel Discussion (Misc queries) | |||
Find, Findnext VBA Loop | Excel Programming | |||
FindNext | Excel Programming | |||
how to find last row/col index within a selected range | Excel Programming | |||
Find...FindNext Problem | Excel Programming |