While Wend Statement

[jex57]

Hi,
Can one use AND in a while wend statement:

While Sheets(DataSheetName).Cells(countCol, 1) <> "" And Sheets(DataSheetName).Cells(countCol, 3) <> "" And Sheets(DataSheetName).Cells(countCol - 1, 1) <> ""

When I use this statement, it does not read the AND statement, so even though there is data in my last column it exits the loops.
Not too sure what I am doing wrong, or if I should not be using While and AND in the same statement.

Any advice would be appreciated.
Thank you

Immediate Window results are:
?sheets(DataSheetName).Cells(countCol - 1, 1)
PartyKey
?Sheets(DataSheetName).Cells(countCol, 1)


False
? Sheets(DataSheetName).Cells(countCol, 3)

 

[Rick Rothstein]

Your variable name (countCol) suggests to me that you have the arguments for the Cells object reversed. The first argument should be the row number and the second argument should be either the column number or the quoted column letter designation.

 

[jex57]

Thanks - variable names are correct - just the way I coded it because of number of counts that I need (column referring to what I am counting and not ROW Column in excel

Have tried brackets and trying to avoid multiple While statements, but does not read the AND clause at all

 

[steve the fish]

Works perfectly well for me. I set up a dummy worksheet to check:

DataSheetName = "sheet1"
countCol = 2

While Sheets(DataSheetName).Cells(countCol, 1) <> "" And Sheets(DataSheetName).Cells(countCol, 3) <> "" And Sheets(DataSheetName).Cells(countCol - 1, 1) <> ""
    MsgBox countCol
    countCol = countCol + 1
Wend

It will run through column A and column C until it finds an empty cell in either.