VBA Help with Loop function

[nikshah22]

Hi,

In column S of my worksheet, there is the formula "=(A2&B2)=(N2&K2)" which results in either a TRUE of FALSE value in each cell under the column, depending on the values in AB and NK. There are several rows with this formula auto-filled in column S.

Now, I have a piece of code that I want to run in a loop as long as there is "FALSE" in any of the cells in column S. Among other things, the code converts these FALSEs into TRUEs. As soon as all cells have "TRUE", the code should stop its loop. This is what I'm using:

'fr is last row of data
Do Until Range("S" & fr + 1) = "FALSE"

'all the code is here

Loop

This partially works because the code does keep looping. However, the loop can't tell when there are no more FALSEs and stops due to an error instead of stopping naturally. I have attached a screenshot of the error. Any help/input is appreciated!

 

[Michael M]

Would be more useful if you posted ALL of the code AND which line error occurs ??

 

[Peter_SSs]

In relation to the loop detecting a FALSE cell, your problem is with this line

Do Until Range("S" & fr + 1) = "FALSE"

Your worksheet formula =(A2&B2)=(N2&K2) is producing logical values of TRUE or FALSE, not text values of "TRUE" or "FALSE"

To resolve that issue change that line to

Do Until Not Range("S" & fr + 1)

If you still have problems then refer to Michael's post.

 

[nikshah22]

Would be more useful if you posted ALL of the code AND which line error occurs ??

Do Until Range("S" & fr + 1) = "FALSE"

        Range("S1700").EntireColumn.Select
        Selection.Find(What:="FALSE", LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate
    
        ActiveCell.EntireRow.Insert
        lr = ActiveCell.Row
        Range("A" & lr, "F" & lr).Delete Shift:=xlUp
        Range("S" & lr).Delete Shift:=xlUp
        
    ThisWorkbook.Sheets("Audit Volume").Range("G" & lr - 1, "J" & lr - 1).Copy ThisWorkbook.Sheets("Audit Volume").Range("G" & lr, "J" & lr)
    ThisWorkbook.Sheets("Audit Volume").Range("L" & lr - 1, "M" & lr - 1).Copy ThisWorkbook.Sheets("Audit Volume").Range("L" & lr, "M" & lr)
    ThisWorkbook.Sheets("Audit Volume").Range("B" & lr).Copy ThisWorkbook.Sheets("Audit Volume").Range("K" & lr)
    ThisWorkbook.Sheets("Audit Volume").Range("A" & lr).Copy ThisWorkbook.Sheets("Audit Volume").Range("N" & lr)
    ThisWorkbook.Sheets("Audit Volume").Range("O5041").Copy ThisWorkbook.Sheets("Audit Volume").Range("O" & lr, "Q" & lr)

    'fr = Worksheets("Audit Volume").Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Range("S5100").Formula = "=(A5100&B5100)=(N5100&K5100)"
    Range("S5100").AutoFill Range("S5100:S" & fr)

Loop

Thanks for you reply! That's the full code and the error occurs in that second batch of code (line 2-5)

Range("S1700").EntireColumn.Select
Selection.Find(What:="FALSE", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate

 

[Peter_SSs]

That's the full code

Really? There is nothing in that code that sets a value for fr. That being the case fr would be zero at best. If fr is zero then you would get a different error on this line Range("S5100").AutoFill Range("S5100:S" & fr)

The error that you initially reported would occur on the line that you have now indicated if the column contains no FALSE values. From your description that will eventually happen as any FALSE values are converted to TRUE.

BTW, what is the name of the active worksheet when this code is run?

 

[nikshah22]

Really? There is nothing in that code that sets a value for fr. That being the case fr would be zero at best. If fr is zero then you would get a different error on this line Range("S5100").AutoFill Range("S5100:S" & fr)

The error that you initially reported would occur on the line that you have now indicated if the column contains no FALSE values. From your description that will eventually happen as any FALSE values are converted to TRUE.

BTW, what is the name of the active worksheet when this code is run?

That's my bad. Completely didn't include fr. Having no problems with it.

Sub Cells_Insert()

ThisWorkbook.Sheets("Audit Volume").Activate

fr = Worksheets("Audit Volume").Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

That's the missing code I didn't include before.

How do I get the TRUE-FALSE error I mentioned to not happen at all? I want the loop to look into column S and only run lines 2-5 onwards if there is a FALSE value left in the column. If column S has all TRUE values, then it should stop the loop and end sub.

 

[Peter_SSs]

I can't say that I really follow what the code is doing (no relevant sample data) but I think this should do what you want and avoid the error.

Sub Cells_Insert_v2()
  Dim fr As Long, lr As Long
  Dim rFalse As Range
  
  ThisWorkbook.Sheets("Audit Volume").Activate
  Application.ScreenUpdating = False
  fr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  Set rFalse = Columns("S").Find(What:="FALSE", LookIn:=xlValues, LookAt:=xlWhole)
  Do Until rFalse Is Nothing
    lr = rFalse.Row
    Rows(lr).Insert
    Range("A" & lr, "F" & lr).Delete Shift:=xlUp
    Range("S" & lr).Delete Shift:=xlUp
    Range("G" & lr - 1, "J" & lr - 1).Copy Range("G" & lr)
    Range("L" & lr - 1, "M" & lr - 1).Copy Range("L" & lr)
    Range("B" & lr).Copy Range("K" & lr)
    Range("A" & lr).Copy Range("N" & lr)
    Range("O5041").Copy Range("O" & lr, "Q" & lr)
    Range("S5100:S" & fr).Formula = "=(A5100&B5100)=(N5100&K5100)"
    Set rFalse = Columns("S").Find(What:="FALSE", LookIn:=xlValues, LookAt:=xlWhole)
  Loop
  Application.ScreenUpdating = True
End Sub

 

[nikshah22]

I can't say that I really follow what the code is doing (no relevant sample data) but I think this should do what you want and avoid the error.

Sub Cells_Insert_v2()
  Dim fr As Long, lr As Long
  Dim rFalse As Range
 
  ThisWorkbook.Sheets("Audit Volume").Activate
  Application.ScreenUpdating = False
  fr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  Set rFalse = Columns("S").Find(What:="FALSE", LookIn:=xlValues, LookAt:=xlWhole)
  Do Until rFalse Is Nothing
    lr = rFalse.Row
    Rows(lr).Insert
    Range("A" & lr, "F" & lr).Delete Shift:=xlUp
    Range("S" & lr).Delete Shift:=xlUp
    Range("G" & lr - 1, "J" & lr - 1).Copy Range("G" & lr)
    Range("L" & lr - 1, "M" & lr - 1).Copy Range("L" & lr)
    Range("B" & lr).Copy Range("K" & lr)
    Range("A" & lr).Copy Range("N" & lr)
    Range("O5041").Copy Range("O" & lr, "Q" & lr)
    Range("S5100:S" & fr).Formula = "=(A5100&B5100)=(N5100&K5100)"
    Set rFalse = Columns("S").Find(What:="FALSE", LookIn:=xlValues, LookAt:=xlWhole)
  Loop
  Application.ScreenUpdating = True
End Sub

This works flawlessly! Thanks a ton

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.