Looping problems

nada

Board Regular
Joined
Jul 4, 2006
Messages
193
Hi! I have a problem with a loop. I want to search the entire worksheet for a cell that contains the text "Sec type". However there is one cell that contains "Sec type" that I do not want to search, ie if that cell is the only one found containing "Sec type" then the search will have found nothig. Thus, the cell (or the row) that I do not want search is SecID.row. Now the loop searches the spreadsheet but stores in c the forbidden SecID cell. My code:

With Range("b1:aa5000")
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

I assume that the problem is on the Loop row in the code but I do not know how to write this. Please help! Thank you very much in advance!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Not sure what you want to do...
Code:
With Range("b1:aa5000")
        Set c = Worksheets("Beräkning").Cells.Find("Sec type", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                If c.Row <> segment.Row And c.row <> secId.row Then
                   ' code to do
                   flg = True
                End If
                Set c = .FindNext(c)
            Loop While c.Address <> firstAddress
            If Not flg Then MsgBox "Not Found"
         Else
            MsgBox "Not Found"
         End If
End With
 

nada

Board Regular
Joined
Jul 4, 2006
Messages
193
hi! sorry i might have been unclear. when the loop is finished I want to set c= Nothing or something to indicate that the I only found "Sec type" once and that was in the forbidden row (secID.row). Please help me with this! Thanks alot!
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Like this?
Code:
With Range("b1:aa5000")
        Set c = Worksheets("Beräkning").Cells.Find("Sec type", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                If c.Row = secId.row Then secId = secId + 1
                found = found + 1
                Set c = .FindNext(c)
            Loop While c.Address <> firstAddress
         Else
            MsgBox "Not Found"
         End If
End With
Set c = Nothing
If found = secId Then MsgBox "Only Found " & found & " cells that are all forbidden"
 

Forum statistics

Threads
1,141,630
Messages
5,707,511
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top