Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looper blooper
I have a loop in which I am trying to search for a cell with a ceratin
textline ("Sec type"). I want to search the entire spreadsheet but if it it possible to search only some used range that is preffered. If I find the cell I am lokking for I want to check to see that it is not on the same row as some other things. These rows are specified by segment.row and secID.row. My problem is that the loop never stops running and I do not know what is wrong with it. I guess it is the Loop-line that is erronous but I do not know how to fix it. Please help me out if can! Thanks! With Range("b1:aa500") Set c = Worksheets("Beräkning").Cells.Find("Sec type", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress And c.row = segment.row Or c.row = secID.row End If End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looper blooper
Dim c as Range, rng as Range
Dim firstAddress as String With Worksheets("Beräkning").Cells Set c = .Find("Sec type",LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do if c.row = segment.row Or c.row = secID.row then if rng is nothing then set rng = c else set rng = Union(rng,c) end if Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With if not rng is nothing then msgbox "Found at " & rng.Address rng.Select else Msgbox "Doesn't match criteria" End if If you want to limit your search to a specific area, change With Worksheets("Beräkning").Cells to With Worksheets("Beräkning").UsedRange or With Worksheets("Beräkning").Range("A1:Z22") as examples. In my experience, I think FIND only looks at the Usedrange by default. -- Regards, Tom Ogilvy "Arne Hegefors" wrote: I have a loop in which I am trying to search for a cell with a ceratin textline ("Sec type"). I want to search the entire spreadsheet but if it it possible to search only some used range that is preffered. If I find the cell I am lokking for I want to check to see that it is not on the same row as some other things. These rows are specified by segment.row and secID.row. My problem is that the loop never stops running and I do not know what is wrong with it. I guess it is the Loop-line that is erronous but I do not know how to fix it. Please help me out if can! Thanks! With Range("b1:aa500") Set c = Worksheets("Beräkning").Cells.Find("Sec type", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress And c.row = segment.row Or c.row = secID.row End If End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looper blooper
Hello Tom! Thank you very much for your help! However I do not seem to get
your code to work. I get error message saying: "Compile error: Loop without Do". I really dont understand that since I just pasted your code and I have the Do statement. Please if you can help me out I would be very grateful! Thank you again Tom!! "Tom Ogilvy" skrev: Dim c as Range, rng as Range Dim firstAddress as String With Worksheets("Beräkning").Cells Set c = .Find("Sec type",LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do if c.row = segment.row Or c.row = secID.row then if rng is nothing then set rng = c else set rng = Union(rng,c) end if Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With if not rng is nothing then msgbox "Found at " & rng.Address rng.Select else Msgbox "Doesn't match criteria" End if If you want to limit your search to a specific area, change With Worksheets("Beräkning").Cells to With Worksheets("Beräkning").UsedRange or With Worksheets("Beräkning").Range("A1:Z22") as examples. In my experience, I think FIND only looks at the Usedrange by default. -- Regards, Tom Ogilvy "Arne Hegefors" wrote: I have a loop in which I am trying to search for a cell with a ceratin textline ("Sec type"). I want to search the entire spreadsheet but if it it possible to search only some used range that is preffered. If I find the cell I am lokking for I want to check to see that it is not on the same row as some other things. These rows are specified by segment.row and secID.row. My problem is that the loop never stops running and I do not know what is wrong with it. I guess it is the Loop-line that is erronous but I do not know how to fix it. Please help me out if can! Thanks! With Range("b1:aa500") Set c = Worksheets("Beräkning").Cells.Find("Sec type", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress And c.row = segment.row Or c.row = secID.row End If End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looper blooper
Just missing an end if
Sub efg() Dim c As Range, rng As Range Dim firstAddress As String With Worksheets("Beräkning").Cells Set c = .Find("Sec type", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do If c.Row = segment.Row Or c.Row = secID.Row Then If rng Is Nothing Then Set rng = c Else Set rng = Union(rng, c) End If End If Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With If Not rng Is Nothing Then MsgBox "Found at " & rng.Address rng.Select Else MsgBox "Doesn't match criteria" End If End Sub -- Regards, Tom Ogilvy "Arne Hegefors" wrote: Hello Tom! Thank you very much for your help! However I do not seem to get your code to work. I get error message saying: "Compile error: Loop without Do". I really dont understand that since I just pasted your code and I have the Do statement. Please if you can help me out I would be very grateful! Thank you again Tom!! "Tom Ogilvy" skrev: Dim c as Range, rng as Range Dim firstAddress as String With Worksheets("Beräkning").Cells Set c = .Find("Sec type",LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do if c.row = segment.row Or c.row = secID.row then if rng is nothing then set rng = c else set rng = Union(rng,c) end if Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With if not rng is nothing then msgbox "Found at " & rng.Address rng.Select else Msgbox "Doesn't match criteria" End if If you want to limit your search to a specific area, change With Worksheets("Beräkning").Cells to With Worksheets("Beräkning").UsedRange or With Worksheets("Beräkning").Range("A1:Z22") as examples. In my experience, I think FIND only looks at the Usedrange by default. -- Regards, Tom Ogilvy "Arne Hegefors" wrote: I have a loop in which I am trying to search for a cell with a ceratin textline ("Sec type"). I want to search the entire spreadsheet but if it it possible to search only some used range that is preffered. If I find the cell I am lokking for I want to check to see that it is not on the same row as some other things. These rows are specified by segment.row and secID.row. My problem is that the loop never stops running and I do not know what is wrong with it. I guess it is the Loop-line that is erronous but I do not know how to fix it. Please help me out if can! Thanks! With Range("b1:aa500") Set c = Worksheets("Beräkning").Cells.Find("Sec type", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress And c.row = segment.row Or c.row = secID.row End If End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looper Blooper | Excel Programming |