Case Method & Range Object Problem

J

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.
 
J

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.
 
J

joel

From look at your code your 8 posibilites are as follows


Row # Col A Col B Col C
iBB_DATA "#N/A" "#N/A" "#N/A"


When "#N/A" you get a 1
When not "#N/A" you get a zero

ResultA = 0
ResultB = 0
ResultC = 0

if Left(rTEST.Cells(iBB_DATA_Row, "A") = "#N/A" then
ResultA = 4
end if
if Left(rTEST.Cells(iBB_DATA_Row, "B") = "#N/A" then
ResultA = 2
end if
if Left(rTEST.Cells(iBB_DATA_Row, "B") = "#N/A" then
ResultA = 1
end if
Result = ResultA + ResultB + ResultC
Select Case ZEROorONE
Case 0
Case 1
Case 2
Case 3
Case 4
Case 5
Case 6
Case 7
end select



What you did is this

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")

Your equation get the following type results

Left(rTEST.Cells(iBB_DATA_Row, 1), 4) = "#N/A" 'This equals TRUE o
False
Left(rTEST.Cells(iBB_DATA_Row, 2), 4) = "#N/A" 'This equals TRUE o
False
Left(rTEST.Cells(iBB_DATA_Row, 3), 4) = "#N/A" 'This equals TRUE o
False


So the resulting equation is something like this
Results = True and True and False

So your equation give TRUE when all three values are #N/A and FALS
otherwise. This give only 2 cases.
 
J

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.
 
J

joel

from
'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")


To
Select Case ISNA(Left(rTEST.Cells(iBB_DATA_Row, 1), 4)) And _
ISNA(Left(rTEST.Cells(iBB_DATA_Row, 2), 4)) And _
ISNA(Left(rTEST.Cells(iBB_DATA_Row, 3), 4))

Case TRUE
Case FALSE
end select



True usally means 1, and False is any other value beside 1. False is
define as NOT TRUE.
 
J

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.
 
J

joel

You have to use my code that I posted yesterday for the 8 cases. As
explained youi method only give two cases.

I've create 8 cases by by giving each result a binary number between
to 7 as you can see in the code below.

Column A B C
0 or 4 0 or 2 0 or 1

this will give thes 8 Conditions false : Not N/A true : N/A
A B C
False False False = 0 + 0 + 0 = 0
False False True = 0 + 0 + 1 = 1
False True False = 0 + 2 + 0 = 2
False True True = 0 + 2 + 1 = 3
True False False = 4 + 0 + 0 = 4
True False True = 4 + 0 + 1 = 5
True True False = 4 + 2 + 0 = 6
True True True = 4 + 2 + 1 = 7


ResultA = 0
ResultB = 0
ResultC = 0

if ISNA(Left(rTEST.Cells(iBB_DATA_Row, "A")) then
ResultA = 4
end if
if ISNA(Left(rTEST.Cells(iBB_DATA_Row, "B")) then
ResultA = 2
end if
if ISNA(Left(rTEST.Cells(iBB_DATA_Row, "C")) then
ResultA = 1
end if
Result = ResultA + ResultB + ResultC
Select Case Result
Case 0
'enter your code here
Case 1
'enter your code here
Case 2
'enter your code here
Case 3
'enter your code here
Case 4
'enter your code here
Case 5
'enter your code here
Case 6
'enter your code here
Case 7
end select
 
J

JingleRock

I guess you mean using 'WorksheetFunction.IsNA'.
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

offset method problem 4
Combine Two Different Worksheet_Change codes 9
Finding last constant in range 1
method "range" of object "_worksheet" fails 5
1004: Activate method of Range class failed 1
Worksheet Change event on different ranges with data validation 2
case sensitive IF, Visual Basic macro IF 2
Select Case "Procedure to large" Error 1

Top