Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Method & Range Object Problem
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Method & Range Object Problem
Addition info 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Method & Range Object Problem
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 or False Left(rTEST.Cells(iBB_DATA_Row, 2), 4) = "#N/A" 'This equals TRUE or False Left(rTEST.Cells(iBB_DATA_Row, 3), 4) = "#N/A" 'This equals TRUE or 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 FALSE otherwise. This give only 2 cases. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199358 http://www.thecodecage.com/forumz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Method & Range Object Problem
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. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199358 http://www.thecodecage.com/forumz |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Method & Range Object Problem
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Method & Range Object Problem
You have to use my code that I posted yesterday for the 8 cases. As I explained youi method only give two cases. I've create 8 cases by by giving each result a binary number between 0 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 -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199358 http://www.thecodecage.com/forumz |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Method & Range Object Problem
I guess you mean using 'WorksheetFunction.IsNA'.
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Method & Range Object Problem
On Apr 29, 12:26*pm, JingleRock wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy method of range object | Excel Programming | |||
Object property/method problem | Excel Discussion (Misc queries) | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Union method for Range Object | Excel Programming | |||
Problem is using any method of Range Object | Excel Programming |