Case Method & Range Object Problem
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 a 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.
|