Do While Loop iterates through worksheet till done, never matches on condition

Question

I have a userform that users enter data into cells that are then sorted onto an Excel spreadsheet 'Main'.

If a user needs to delete an entry, they tab over till the offending entry is on the leftmost side of the userform and hit delete, an option box appears and asks them if they wish to delete 'yes' or 'no'. If they hit 'yes' the userform is supposed to clear the cells of the offending data while on the spreadsheet 'Main' it goes row by row and matches the value from the 'Auth1' to its counterpart somewhere in the 'A' column, deleting it.

Neither is happening. Putting in breaks, and stepping through the code and program, I have a Do While Loop that goes until it runs out of cells to check and then proceeds with the rest of the code.

It blew right passed the matching cell that no longer made the condition 'true'.

The code snippet in question:

    Do While UMAuthorization!Auth1 <> ActiveCell
        ActiveCell.Offset(1, 0).Activate
    Loop

The 'Yes' sub in total

 Private Sub DeleteYes_Click()
   DeleteForm.Hide
   CloseBook
   OpenMinimized
   Range("A2").Select
   Sheets("Main").Unprotect
       Do While UMAuthorization!Auth1 <> ActiveCell
          ActiveCell.Offset(1, 0).Activate
       Loop
   ActiveCell.EntireRow.Delete Shift:=xlUp
   Sheets("Main").Protect
SaveBook
   Range("A2").Select
    If Auth2 <> "" Then
        Do While UMAuthorization!Auth2 <> ActiveCell
            ActiveCell.Offset(1, 0).Activate
        Loop
    Else
        Do While UMAuthorization!Claim1 > ActiveCell And ActiveCell <> ""
            ActiveCell.Offset(1, 0).Activate
        Loop
    End If
End Sub

We just updated to 2016 excel. This code runs perfectly on 2010. To me, it makes total sense. However, Excel 2016 isn't acknowledging it. I've checked references and tinkered with values, but I'm stumped here. Thoughts anyone?

Edit: Here's some nonsensical sample data. Auth is column A. Row 1 is a static header.

1 Auth    Claim  TaxID    Type   AuthDate   
2 0033087 154255 270275455    PT     08/25/18
3 0094525 155512 545465646    PT     12/12/18
4 0025125 555555 565445546    Acu    11/15/12
5 9994313 354585 564645545    X      01/08/18
6 5464654 111551 688558585    Chiro  09/15/09 
7 9954545 445664 545665456    OT     05/30/15

Without at least some sample data, and an explanation of what `UMAuthorization!Auth1` etc are (objects on your form maybe?) this Q is impossible to answer. — chris neilsen, Jan 08 '19 at 21:29
Isn't ActiveCell a range object and UMAuthorization!Auth1 a value from your form? You might try Activecell.value. Also, when deleting cells and shifting up, you should start at the bottom of your data, so you do not miss any matches. — mooseman, Jan 08 '19 at 21:30
And BTW, code so reliant on Active Sheet, Active Cell is _always_ fragile and hard to debug and maintain. [See this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for ways to avoid them — chris neilsen, Jan 08 '19 at 21:32
@Om3r and @chrisneilsen I cannot share the spreadsheet since it contains client data. Column A is just authorization codes. The form is looking for the authorization code that matches the number value in the cell Auth1 on the form. `UMAuthorization!Auth1` is the form value. The form is called `UMAuthorization`. `Auth1` is the a cell in the form `UMAuthorization`. The `!` symbol stands for single data type. I didn't write this macro, it predates me by a few years in this position. — GingerSnipSnap, Jan 08 '19 at 22:01
You could try to change Do While UMAuthorization!Auth1 <> ActiveCell to Do While UMAuthorization!Auth1 <> ActiveCell.value — mooseman, Jan 08 '19 at 22:04
@GingerSnipSnap you don't need to share the workbook itself, but please edit into your Q some representative data (doesn't have to be _real_ data) that when you run your code on it you see the issue described in your Q. And, you say `Auth1` is "a cell in the form". Well, forms don't have _cells_, they have TextBox's, ComboBoxes's, ListBoxes's etc. Please be specific (and edit that info into your Q too) — chris neilsen, Jan 08 '19 at 22:06
At first glance, your data appears to be numeric, and your form (forms always do this) data will be string. Try changing the `Do While`tests to `... <> ActiveCell.Text` — chris neilsen, Jan 08 '19 at 22:28

score 0 · Answer 1 · answered Jan 08 '19 at 22:09

You chould replace the code between

Sheets("Main").Unprotect

and

Sheets("Main").protect

With this

With Worksheets("Main").Range("A:A")
    Set c = .Find(UMAuthorization!Auth1, LookIn:=xlValues)
    If Not c Is Nothing Then
        Do
            c.EntireRow.Delete Shift:=xlUp
            Set c = .Find(UMAuthorization!Auth1, LookIn:=xlValues)
        Loop While Not c Is Nothing
    End If
End With

It will find the value from your form and delete all rows that match in column A.

You can restrict the range by changing column A to some range like A2:A200.

If you want to only delete the first match then remove the do loop but keep the delete row line.

Since I don't have your form, I tested this with a static value.

this code didn't work for me either. At first, the code had excel hanging in a continuous 'busy' stasis, so I had to Task Manager excel to force the quit. I restricted the ranges in the A column and it worked like it should, but again, the end result was the same. The selected data for deletion on the userform did not delete from userform or the spreadsheet. — GingerSnipSnap, Jan 09 '19 at 15:21

Ama · Answer 2 · 2019-01-08T23:18:39.977

This may have to do with the way Excel interprets your data. I can see you have a series of "heading" zeros in your data sample.

The default object property for a Range Object such as ActiveCell is .value. I would try changing ActiveCell by ActiveCell.value2. For information about the differences, see this related post: What is the difference between .text, .value, and .value2?

Do While UMAuthorization!Auth1 <> ActiveCell.value2
    ActiveCell.Offset(1, 0).Activate
Loop

Another lead would be to run a cLng() to convert both variables into a number where "heading" zeros will be "trimmed".

Do While cLng(UMAuthorization!Auth1) <> cLng(ActiveCell)
    ActiveCell.Offset(1, 0).Activate
Loop

Alternatively, you could force the zeros to appear if the code has less than 7 digits:

Do While format(UMAuthorization!Auth1, "0000000") <> format(ActiveCell, "0000000")
    ActiveCell.Offset(1, 0).Activate
Loop

Let us know if the Loop is still runing for ever?

More generally, I would recommend avoiding relying on .activate and ActiveCell object to scroll through your data as it is both slow and risky if the user clicks somewhere on the sheet whilst the Loop is running. You can efficently extract your data into an array, process your array of data within VBA to identify the relevant Row(s) and then process the Sheet: (Note this will also allow you to see how Excel reads your data by throwing a debug.print on the data stored into the Array)

Dim mySheet as new worksheet
set mySheet = ActiveSheet 'Or something else

Dim arrMyData() as variant 'Must be a variant
arrMyData = range("A2:A999").Value2 'Adjust A999 with your case

dim i as long
for i = lBound(arrMyData,1) to uBound(arrMyData,1)
    debug.print "Comparing " & arrMyData(i,1) & " with " & UMAuthorization!Auth1
    if arrMyData(i,1) = UMAuthorization!Auth1 then
    'if cLng(arrMyData(i,1)) = cLng(UMAuthorization!Auth1) then  <- Use this line if the one aboe does not work
        mySheet.Rows(i+1).delete Shift:=xlUp 'i+1 Because i=1 because your array starts at Row 2 (Cell A2)
        Debug.Print "Deleted row " & i+1
    end if
Next

Your suggestion to use `CInt` is (almost) a good one, except that some of the values will exceed the capacity of an Ìnteger`. Use `CLng` instead — chris neilsen, Jan 08 '19 at 23:06

Do While Loop iterates through worksheet till done, never matches on condition

2 Answers2