# Help with the Do While Loop



## PresidentEvil (Dec 17, 2022)

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 (Dec 17, 2022)

Hi

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


----------



## PresidentEvil (Dec 17, 2022)

mohadin said:


> 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 (Dec 17, 2022)

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 (Dec 17, 2022)

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 (Dec 17, 2022)

mohadin said:


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


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 (Dec 17, 2022)

PresidentEvil said:


> 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 (Dec 17, 2022)

MARK858 said:


> Are you sure that is what happens?
> with this code
> 
> ```
> ...


Trying that now. 

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



PresidentEvil said:


> 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 (Dec 17, 2022)

PresidentEvil said:


> 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 (Dec 17, 2022)

MARK858 said:


> Maybe...
> 
> ```
> Dim myCell As Range
> ...


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


----------



## PresidentEvil (Dec 17, 2022)

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
```


----------



## MARK858 (Dec 17, 2022)

PresidentEvil said:


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


It isn't supposed to stop at "Not Authorized" in B Column as you stated


PresidentEvil said:


> skip the row if the value contains 'Not Authorized' and just move to next row and continue with the loop.



and you didn't ask for the stop at blanks in column A in that part of the question.

The code in post number 4 by Mohadin does stop at both blanks and Not Authorized (if you change "Access Denied" to "Not Authorized")


----------



## PresidentEvil (Dec 17, 2022)

MARK858 said:


> It isn't supposed to stop at "Not Authorized" in B Column as you stated
> 
> 
> and you didn't ask for the stop at blanks in column A in that part of the question.
> ...


I made it a bit complicated since the other steps after this code are executed are very important and highly dependant on blanks or the text not authorized,

I found another way to execute this without a loop. Both the codes worked after making a few alterations.


----------



## MARK858 (Dec 17, 2022)

Happy you worked it out


----------



## PresidentEvil (Dec 17, 2022)

MARK858 said:


> Happy you worked it out


Thank you for helping out.


----------

