Help with the Do While Loop

[PresidentEvil]

Hi,

Code mentioned below has an issue to it. I have two columns ( A & B )

A contains unique IDs
B contains values

I'm using a macro with a loop to update the values in B column for respective IDs. However, the condition to stop the loop is not working. The null value condition is working without a problem but the condition after 'OR' operator is not working. Purpose is to stop the loop when there is a blank in column A or has the text "Access Denied" in column B.

Any hep is highly appreciated.

Set indexSheet = ActiveSheet
Sheets("Update").Select
irow = 2
A1 = 2
Do While indexSheet.Cells(irow, 1).Value <> vbNullString Or indexSheet.Cells(irow, "B").Value <> "Not Authorized"

' codes to run in the loop is here (I use SENDKEYS)

irow = irow + 1
A1 = A1 + 1
Loop
On Error GoTo 0
[/CODE

 

[mohadin]

Hi

Do While indexSheet.Cells(irow, 1).Value <> 0 Or indexSheet.Cells(irow, "B").Value <> "Not Authorized"

 

[PresidentEvil]

Hi

Do While indexSheet.Cells(irow, 1).Value <> 0 Or indexSheet.Cells(irow, "B").Value <> "Not Authorized"

Thank you for the response. The first check is working fine with vbNullString. The issue is with the second one "Not Authorized".

Is it connected somewhat to that vbNullString and hence not checking the second condition ? I'll try with 0 in the first check, but want to understand what's going wrong there.

 

[mohadin]

so what about

Do While (indexSheet.Cells(irow, 1).Value <> vbNullString And indexSheet.Cells(irow, "B").Value = "Access Denied") = False

"Access Denied" OR "Not Authorized"

 

[PresidentEvil]

Alternatively, if there is some code that can be used to skip the row if the value contains 'Not Authorized' and just move to next row and continue with the loop.

Ex: if there are 10 rows with IDs and value. And macro is looping through each row and executing the command. Row 7 with ID and value, the value is 'Not Authorized'. The loop should skip that row and jump to 8th row and continue the loop.

 

[PresidentEvil]

so what about

Do While (indexSheet.Cells(irow, 1).Value <> vbNullString And indexSheet.Cells(irow, "B").Value = "Access Denied") = False

"Access Denied" OR "Not Authorized"

I'll probably use 'Not Authorized'. But this code has the AND Operator which means it takes both the things into consideration before exiting the loop. It should be either 1st one or second one. If one of these is true then it should exit the loop.

 

[MARK858]

But this code has the AND Operator which means it takes both the things into consideration before exiting the loop. It should be either 1st one or second one. If one of these is true then it should exit the loop.

Are you sure that is what happens?
with this code

Sub xxxxx()
    Dim indexSheet As Worksheet, irow As Long, A1xx As Long
    Set indexSheet = ActiveSheet
    'Sheets("Update").Select
    irow = 2
    A1xx = 2
    Do While indexSheet.Cells(irow, 1).Value <> vbNullString And indexSheet.Cells(irow, "B").Value <> "Not Authorized"

        ' codes to run in the loop is here (I use SENDKEYS)

        irow = irow + 1
        A1xx = A1xx + 1
    Loop
    MsgBox indexSheet.Cells(irow, 1).Row
End Sub

For me it stops at row 9 with the below

and at row 3 with the below

 

[PresidentEvil]

Are you sure that is what happens?
with this code

Sub xxxxx()
    Dim indexSheet As Worksheet, irow As Long, A1xx As Long
    Set indexSheet = ActiveSheet
    'Sheets("Update").Select
    irow = 2
    A1xx = 2
    Do While indexSheet.Cells(irow, 1).Value <> vbNullString And indexSheet.Cells(irow, "B").Value <> "Not Authorized"

        ' codes to run in the loop is here (I use SENDKEYS)

        irow = irow + 1
        A1xx = A1xx + 1
    Loop
    MsgBox indexSheet.Cells(irow, 1).Row
End Sub

For me it stops at row 9 with the below

View attachment 81105

and at row 3 with the below

View attachment 81106

Trying that now.

Is it possible to do the below instead? Not stop but skip if there are more values after 'Not Authorized'?

Alternatively, if there is some code that can be used to skip the row if the value contains 'Not Authorized' and just move to next row and continue with the loop.

Ex: if there are 10 rows with IDs and value. And macro is looping through each row and executing the command. Row 7 with ID and value, the value is 'Not Authorized'. The loop should skip that row and jump to 8th row and continue the loop.

 

[MARK858]

Is it possible to do the below instead?

Maybe...

    Dim myCell As Range
    For Each myCell In indexSheet.Range("B2:B" & indexSheet.Range("A" & Rows.Count).End(xlUp).Row)
        If LCase(myCell.Value) <> "not authorized" Then MsgBox "Do something here"
    Next

 

[PresidentEvil]

Maybe...

    Dim myCell As Range
    For Each myCell In indexSheet.Range("B2:B" & indexSheet.Range("A" & Rows.Count).End(xlUp).Row)
        If LCase(myCell.Value) <> "not authorized" Then MsgBox "Do something here"
    Next

Tried it. It's not stopping/skipping at "Not Authorized" in B Column or blank cell in A column.