Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

Loop til you drop

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

[963 byte] By [AndersBank] at [2007-12-22]
«« How to release large unused/allocated space
»» ThreeWayMerge.Run() crashes when IsBaseless is set to true
# 1

Hi

Your problem is the extra conditions in the loop statement. If you have the blue elements only this will stop the loop when it scrolls back to the first item again, the extra conditions make the loop continue through the loop again past the first item again

Loop While Not c Is Nothing And c.Address <> firstAddress And c.row = segment.row Or c.row = secID.row

ADG at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 2
hi! Yes I understand that but how do I check to see that I have the right address then? If I do not use the statements the code will stop the first time the line is found which is not necesseraly the line I am looking for.
AndersBank at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 3

Hi

I would probably make the logic a liitle easier to follow by having a boolean value to test (KeepSearch in the below)

KeepSearching = True
FIRSTADDRESS = c.Address

Do
Set c = .FindNext(c)
If ((c.Address = FIRSTADDRESS) Or (c Is Nothing)) Then
KeepSearching = False
ElseIf c.Row = segment.Row Then
KeepSearching = True
ElseIf c.Row = secID.Row Then
KeepSearching = True
Else
KeepSearching = False
End If
Loop While KeepSearching = True

ADG at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 4
This is the sort of routine I've used before, hope it's useful:

Sub SearchText()
'finds the text "sec type" on Sheet 1 and counts the number of occurences when it isn't on the
'same row as the text "segment" or "secID"
Dim Rng As Range
Dim firstAddress As String
Dim result As Range
Dim segment As Range
Dim secID As Range

Count = 0
Set segment = ThisWorkbook.Worksheets("sheet1").Range("A4") 'range contains "segment"
Set secID = ThisWorkbook.Worksheets("sheet1").Range("A5") 'range contains "secID"

Set Rng = ThisWorkbook.Worksheets("sheet1").Range("a1:aa500")

With Rng
Set result = .Find("Sec type", LookIn:=xlValues) 'find text
If Not result Is Nothing Then
firstAddress = result.Address 'get address of first found text
Do
If result.Row <> segment.Row And result.Row <> secID.Row Then 'not on segment/secID rows?
Count = Count + 1 'do stuff here
End If
Set result = .FindNext(result)
Loop While Not result Is Nothing And result.Address <> firstAddress
End If
End With
MsgBox Count
End Sub
Navajo at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
Microsoft ISV Community Center Forums
Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified