whats wrong with my loop??

[Spyros13]

CODE WORKS. BUT LOOP IS LOPPING MORE THAN ONE NORMAL TIME TO GET RESULTS AND MOVING TO NEXT BIT. iT LOOPING TOO MANY TIMES OVER AND OVER AGAIN, REPEATING ITSELF (GETS THE RIGHT RESUTS THOUGH, BUT REPEATED RESULLTS), BEFORE FINISHING A SECTION AND MOVING ON TO THE NEXT SHEET. CAN ANYONE HELP ,ME/FIX MY CODE?

SO ITS NOT AN INFINITE LOOP, ITS NOT STUCK, BUT PERHAPS NOT WELL WRITTEN.

This is a piece of code I put together last night pushing to do so, in a rush, to ''detect'' #REFS! my way. Im quite pleased with the simpleton but obvious to me way I am seeking any error or ref in the spreadsheets, with my code.

However, That's not what my question is about. My question is : why is my search looping over x2, x3, (and maybe more) time per worksheet.??

My question is not about the loop over the worksheets. I can live with that and that works quite fine (taking that bit from the internet and there are other ways too , and have used it before. so that but works fine). Its about the internal loop which does the actual checking over the used areas, the columns and rows. I did it in a rush and Im still shacking, as I stlll cant concentrate on it clearly properly because shannanigans I feel work & the vapidly young criminal areas i live in is making nurveous/nuts. (althiugh I could be paranoid, hungry exhausted idk. so Ignore the last phrase. ) This post might have to be deleted edied in future. Yes i know , to many I appear mad/craxey

Sub FindRefsandErrorsinWorkbookBySheets()
    Dim lastColumn As Integer

    Dim myCell As Range
    Dim LastRow As Long
   

Dim ws As Worksheet
'Dim starting_ws As Worksheet
'Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning
       
For Each ws In ThisWorkbook.Worksheets
    ws.Activate
   
lastColumn = ws.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

Dim usedRows As Long

usedRows = ws.UsedRange.Rows.Count
   
   
    For cols = 1 To lastColumn
    
    LastRow = Cells(usedRows, cols).End(xlUp).Row

    For Each myCell In Range(cols & ":" & LastRow + 1) ' & ":" & LastRow + 1
   
      If myCell.Text = "#REF!" Then
      myCell.Offset(0, 1) = "This was a ref in " & myCell.Address
     
      MsgBox "Ref Found in " & myCell.Address
      
         Else:
            If IsError(myCell.Value) Then
            myCell.Offset(0, 1) = "Do you know you had different tyoe of error in " & myCell.Address & "???"
           
            End If
     End If
    Next myCell
   
    Next cols
   
     
            MsgBox ActiveWorkbook.Worksheets(I).Name

         Next ws

      End Sub

I think I am glad at just about getting it to loop as if it was a normal

Range("A1:A"&LastRow+1)

call, and a. atleast it worked b... moved/does loop. But it loops too many times before moving on to the next worksheet.

I think this is causing it :

For Each myCell In Range(cols & ":" & LastRow + 1)

, i.e. is that not right?? But that piece that is making it work (althiugh it does *x times, too many times, going back on itself an repeating. Thats what I need to fix and sort out.)

Whats the proper way to write what I've done and trying to do?? How should write that , or what should I declare-do to make it loop maturely/sanely (as it was doing when it was only a 1 dimensional single range)??

I have a hint hunch my whole code is much too innefcient for you guys here, and too basic, but since I am completely on my own in excel and an eager "newbie novice" at this doing it inbetween jobs and other thinhgs, Im happy that its working, that It works (even tho its is appearing to work ''badly"). I just need to address that way im looping-'caling the ranges/areas to search' (also found that - I THINK - it seems so by eye - that its looping down and accross rows at the Same Time!?)

Try it yourselves, and please help me out. What have I done wrong/how can i improve the looping at least, and maybe is there other things I neeed to drop/add change to the whole thing to work for more straughtforward/efficeitnly.??

Many thanks for your help and time in advance,

S.DW

any assistance?? help?? would be sincerely appreciated.

 

[VBE313]

So what do you need to do? Loop through each tab and change the #REF! fields to "This was a ref in A10"? Please post an example picture.

 

[Spyros13]

 

[Spyros13]

No . I dont want to do that. I can do that on my own too. Its not a big problem or what I am asking.

As you can see from picture my macro already works. already does what I want . It gives me message boxes too (which I can take out) , Its the way it loops repeteteivky over the sheets before moving on to the next sheet I need to fix.

The pic above is my crazey testing . It does what I want. I don't wNt anything more. I just need to fix this looping 'bug'.

I just need to fix the bit that loops over these ranges.

I think its this bit :

For Each myCell In Range(cols & ":" & LastRow + 1)

- this is what is both making it work and also looping over more than once per sheet. Something I need to do/ammend to fix sort that .

 

[Fluff]

There is no bug in your code, it's just the way you have written it & it is doing exactly what you have told it to do.
Why not just loop through the used range?

 

[Spyros13]

I did what I did because I want to not want to do something like this:

For i = 1 To LastRow
            For j = 3 To LastCol

or the other way around .

Ok, Thats what I wrote from before I saw your reply @Fluff . How do you do that? (ill google something about that later if you cant tell me now). Thanks for the tip pointer though. Ill probably find something so that was helpful.

So what is

For Each myCell In Range(cols & ":" & LastRow + 1)

telling it to do? - particularly the

cols & ":" & LastRow

bit? cross over the amount of row x column times? I don't get it. (please dont tell me now The kids not mine - are diving me beserk)

All i tried to do is replicate:

For Each myCell In Range("A1:A" & LastRow)

which works nicely for a single column. Ofcourse, I couldn't iterarate over A,B,C.....ZZ as letters. and I tried interpreting it to cell(i,j) form (very quickly late in evening). It was 02:00 had a one heck of a day, am I lost pateince and finally pushed-forced

Range(cols & ":" & LastRow + 1)

. It was the nearest I could get to it last night . Today I didnt want to spend my day stressing about something at work either not of my making, so i am slightly aggrieved.

Ill google looping over the used range later. Maybe Laila can help me. Or rick. Ill find something. Don't worry. I must eat & breath now. Bless.

 

[Fluff]

On the 1st iteration of that loop, cols is 1 & if LastRow was 33 you would get

For Each myCell In Range( "1:34")

which means it looping through every cell in rows(1:34)
To loop through the cells you can use

Sub FindRefsandErrorsinWorkbookBySheets()
    Dim lastColumn As Integer

    Dim myCell As Range
    Dim LastRow As Long
   

Dim ws As Worksheet
'Dim starting_ws As Worksheet
'Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning
       
   For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      For Each myCell In ws.UsedRange
         If myCell.Text = "#REF!" Then
            myCell.Offset(0, 1) = "This was a ref in " & myCell.Address
            
            MsgBox "Ref Found in " & myCell.Address
            
         ElseIf IsError(myCell.Value) Then
            myCell.Offset(0, 1) = "Do you know you had different tyoe of error in " & myCell.Address & "???"
         End If
      Next myCell
      MsgBox ActiveWorkbook.Worksheets(i).Name
      
   Next ws

End Sub

If the cells with errors all contain a formula then you can speed it up like

For Each myCell In ws.UsedRange.SpecialCells(xlFormulas, xlErrors)

 

[Spyros13]

Thank You Sir.

My computer crashed inbetween reading your first post , and reading your 2nd reply, and then I saw Loop Through Cells Inside the Used Range (For Each Collection Loop) - Xelplus - Leila Gharani and applied that.. Now Ive seen your reply version, and will be using that . Thank you for your help. I couldn't have done it without you. Thank you.

& what you did,

For Each myCell In ws.UsedRange

is faster then what I just did before the crash which was

For Each myCell In ActiveSheet.UsedRange

- which worked (but my comp crashed soon after and was a little bit slower i think, or is there no difference? It doesnt matter, it works! Ive been told not to avoid activesheet, select and activate , so thank you twice! & Ill use yours. ! Thank you Sir Again !!

p.s. & yes they do, so il use that !

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Spyros13]

You know though, I am still wondering if I could (& how I could) to make it work for range(integera:integerb) kind of thing. It seems that that is discourged in VBA? or slower anyway? Sorry for continuing convo but brain cant stop thinking & talking outloud. Even if its slower (as it seems), I still find it fascinating. Ill ponder on it on my own. Thanks for all you done anyway. Ill ponder on it later . cheers.