Case Method & Range Object Problem

[JingleRock]

I am working with 2 Sheets and 3 Range Objects:

Set wsDATA = Worksheets(SHEET_SOURCE)
Set wsWORKPLACE = Worksheets(SHEET_WORKPLACE)

Set rSID = wsDATA.Range("A5:A563")
Set rTEST = wsDATA.Range("M5:O563")
Set rDEST = wsWORKPLACE.Range("AC5:AI563")

I am using the Case Method with 8 Case possibilities plus a 'Case
Else'. My Code's first Case possibility is:

Select Case ZEROorONE

'POSSIBILITY #1 - 3 CELLS = 0 ("BAD" DATA)
Case (Left(rTEST.Cells(iBB_DATA_Row, 1), 4) = "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) = "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) = "#N/A")

iTST_1_POSS = iTST_1_POSS + 1

'NO "GOOD" DATA TO COPY
rDEST.Cells(iWP_Row, 1) = "13" '<< DEF CELL REF TO wsDATA
'NAMED RANGE'
rDEST.Cells(iWP_Row, 2) = rSID.Cells(iBB_DATA_Row, 1)
rDEST.Cells(iWP_Row, 6) = "ZERO RTGS"

iWP_Row = iWP_Row + 1
ActiveCell.Offset(1, 0).Select
iBB_DATA_Row = iBB_DATA_Row + 1

Before invoking the Case Method, my Code has several MsgBox's; in each
of these Box's the correct value is returned for each of the variables
requested. Also, the correct values are "written" to the specified
locations in the rDEST Range. However, the Case Method does not
always select the appropriate Case possibility (I can see this when I
step thru my Code), using the same variables as specified in the
MsgBox's.

I find this VBA behavior very strange. Help.

 

[JingleRock]

Addition info re: my Code:

Prior to MsgBox's, my Code has:

'BELOW IS ADJUSTED TO REFLECT POSITION IN EXTRACTED RANGE
iBB_DATA_Row = 1
iWP_Row = 1

Over and out.

 

[JingleRock]

Joel,

Thanks very much for your rapid and extensive response.

I apologize for not being more complete in my original plea.

I am dealing with only strings: the data is ratings information --
either the first 4 characters are '#N/A' (actually, I consider this to
have a value of zero) or "good" ratings info (actually, I consider
this to have a value of one).
I have a 'Do While ... Loop' involving about 600 securities; for each
security, I have 3 pieces of rtgs info (so, I have 2 x 2 x 2, or 8
Case possibilities; also, for 4 of these possibilities, I have to test
for non-duplicate string values). At first, I was using 'If ...
Then ... Else' stmts, but the coding was becoming EXTREMELY complex,
so I switched to the Case Method.

Using my terminology, the 8 possibilities are: 0 0 0 / 0 0 1 / 0 1
0 / 0 1 1 / 1 1 1 / 1 1 0 / 1 0 1 / 1 0 0. In my Code, I refer to the
3 pieces of rtgs info as DEF (for Default), SUB CELL_1, SUB CELL_2
(for potential Substitutes).

I am posting the remainder of my Case Method below (I did not include
Code for the testing of non-duplicate string values in Cases 5, 6, 7.
Actually, Case 5 could have 3 identical string values, or any of 3
sets of twin string values.)

'POSSIBILITY #2 - ONLY SUB CELL_2 = 1 ("GOOD" DATA)
Case (Left(rTEST.Cells(iBB_DATA_Row, 1), 4) = "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) = "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) <> "#N/A")

'COPY SUB CELL_2
rDEST.Cells(iWP_Row, 1) = "14" '<< SUB CELL_2 REF TO
wsDATA 'NAMED RANGE'
rDEST.Cells(iWP_Row, 2) = rSID.Cells(iBB_DATA_Row, 1)
rDEST.Cells(iWP_Row, 4) = rTEST.Cells(iBB_DATA_Row, 3)

iWP_Row = iWP_Row + 1
ActiveCell.Offset(1, 0).Select
iBB_DATA_Row = iBB_DATA_Row + 1

'POSSIBILITY #3 - ONLY DEF CELL = 1 ("GOOD" DATA)
Case (Left(rTEST.Cells(iBB_DATA_Row, 1), 4) = "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) <> "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) = "#N/A")

'DO NOT COPY DEF CELL
rDEST.Cells(iWP_Row, 1) = "13" '<< DEF CELL REF TO wsDATA
'NAMED RANGE'
rDEST.Cells(iWP_Row, 2) = rSID.Cells(iBB_DATA_Row, 1)

iWP_Row = iWP_Row + 1
ActiveCell.Offset(1, 0).Select
iBB_DATA_Row = iBB_DATA_Row + 1

'POSSIBILITY #4 - DEF CELL = 1 & SUB CELL_2 = 1 ("GOOD" DATA)
(DUPLICATEs?)
Case (Left(rTEST.Cells(iBB_DATA_Row, 1), 4) = "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) <> "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) <> "#N/A")

'TEST FOR DUPLICATEs
'BELOW IS 'If' #1
If rTEST.Cells(iBB_DATA_Row, 2) =
rTEST.Cells(iBB_DATA_Row, 3) Then '<< A DUPLICATE

'DO NOT COPY SUB CELL_2 WHEN THERE IS A DUPLICATE W/
DEF CELL
rDEST.Cells(iWP_Row, 1) = "13" '<< DEF CELL REF TO
wsDATA 'NAMED RANGE'
rDEST.Cells(iWP_Row, 2) = rSID.Cells(iBB_DATA_Row, 1)
rDEST.Cells(iWP_Row, 6) = "DUPE RTGS CELL_2"

iWP_Row = iWP_Row + 1

ActiveCell.Offset(1, 0).Select
iBB_DATA_Row = iBB_DATA_Row + 1

Else '<< 'If' #1 - THERE IS NOT A DUPLICATE

'COPY DEF CELL & SUB CELL_2
rDEST.Cells(iWP_Row, 1) = "13" '<< DEF CELL REF TO
wsDATA 'NAMED RANGE'
rDEST.Cells(iWP_Row, 6) = rTEST.Cells(iBB_DATA_Row, 2)
rDEST.Cells(iWP_Row, 7) = rTEST.Cells(iBB_DATA_Row, 3)

iWP_Row = iWP_Row + 1
ActiveCell.Offset(1, 0).Select
iBB_DATA_Row = iBB_DATA_Row + 1

End If '<< 'If' #1

'POSSIBILITY #5 - 3 CELLS = 1 ("GOOD" DATA)(DUPLICATEs?)
Case (Left(rTEST.Cells(iBB_DATA_Row, 1), 4) <> "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) <> "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) <> "#N/A")

'TEST FOR DUPLICATEs

'POSSIBILITY #6 - DEF CELL = 1 & SUB CELL_1 = 1 ("GOOD" DATA)
(DUPLICATEs?)
Case (Left(rTEST.Cells(iBB_DATA_Row, 1), 4) <> "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) <> "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) = "#N/A")

'TEST FOR DUPLICATEs

'POSSIBILITY #7 - SUB CELL_1 = 1 & SUB CELL_2 = 1 ("GOOD" DATA)
(DUPLICATEs?)
Case (Left(rTEST.Cells(iBB_DATA_Row, 1), 4) <> "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) = "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) <> "#N/A")

'TEST FOR DUPLICATEs

'POSSIBILITY #8 - ONLY SUB CELL_1 = 1 ("GOOD" DATA)
Case (Left(rTEST.Cells(iBB_DATA_Row, 1), 4) <> "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) = "#N/A" And _
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) = "#N/A")

'COPY SUB CELL_1
rDEST.Cells(iWP_Row, 1) = "12" '<< SUB CELL_1 REF TO
wsDATA 'NAMED RANGE'
rDEST.Cells(iWP_Row, 2) = rSID.Cells(iBB_DATA_Row, 1)
rDEST.Cells(iWP_Row, 3) = rTEST.Cells(iBB_DATA_Row, 1)

iWP_Row = iWP_Row + 1
ActiveCell.Offset(1, 0).Select
iBB_DATA_Row = iBB_DATA_Row + 1

Case Else

MsgBox "TILT"

End Select

The last two sentences of your post are exactly the way I want Case 1
to work; and when all 3 string values = #N/A, it is working correctly;
however, Case 1 is being selected when all 3 string values are NOT =
#N/A.
Obviously, I am missing some of the logic of the Case Method. I think
the solution has something to do with grouping equations with
parentheses, but I am not sure how.

 

[JingleRock]

Joel,

I have 8 unique sets of statements that need to be executed; the key
is specifying each of the Cases so that each is selected to be True
ONLY when the appropriate set of rating info is being processed.

I tested the following code as my POSSIBILITY #1:

Case Left(rTEST.Cells(iBB_DATA_Row, 1), 4) = "#N/A"

When stepping-thru my Code, this Case would be selected when the data
cell was equal to a "good" value, like "AAA", but when the data cell
was equal to "#N/A", this Case would NOT be selected. This is
backwards from what I would expect.
Over and out.

 

[JingleRock]

I guess you mean using 'WorksheetFunction.IsNA'.

 

[JingleRock]

I guess you mean using 'WorksheetFunction.IsNA'.

Thanks very much, Joel.

I did some experimenting; first with just one expression in Case 1 and
then with all three expressions in Case 1. I discovered that if I
swapped logical ORs with logical ANDs and then swapped "<>"s with
"="s, all 8 of the Cases work correctly; this is true for the 600+
securities in my wsDATA Sheet.

Thanks again.