Select Case on different worksheet?
 

Method or Data Member not Found in SELECT CASE for worksheet "DataDown".


This code resides in the "ThisWorkbook" code module.

I am trying to run down a column and count the number of vehicle
accidents by type in worksheet "DataDown" in Range V8 to last row
occupied (there may be some blanks in the rows and number of rows will
vary).

I then need to put the totals on worksheet "Data" in Column B8.

The SELECT CASE does not seem to like the different worksheet
references?

--------------------------------
Sub Acc_Types()

Dim wbBook As Workbook
Dim wsSheet As Worksheet

Dim i As Integer 'Loop counter
Dim iOtherParty As Integer 'OTHER PARTY HIT REAR OF
DRIVER
Dim iParkingBacking As Integer 'PARKING/BACKING
Dim iPedestrian As Integer 'PEDESTRIAN COLLISION
Dim iOtherFailedtoYeild As Integer 'OTHER PARTY FAILED TO YIELD
Dim iDmgWhileParked As Integer 'DMG WHILE PARKED/OTH PARTY
CUS
Dim iMiscComp As Integer 'MISC. COMPREHENSIVE
Dim iDrivLostControl As Integer 'DRIV LOST CONTROL OF
VEHICLE
Dim iDrivFailClearance As Integer 'DRIV FAILED TO OBSERVE
CLEARANCE
Dim iHitRear As Integer 'HIT REAR OF OTHER
PARTY
Dim iVehicleFailure As Integer 'VEHICLE FAILURE
Dim iOther As Integer 'All other
descriptions

Dim rDescription As Range 'Description Column

Set wbBook = Workbooks("Charts_DataDown.xls")
Set wsSheet = wbBook.Sheets("DataDown")

Worksheets("DataDown").Activate 'Move to "DataDaown" worksheet

i = 0 'Loop counter
iOtherParty = 0 'Set all counters to zero
iParkingBacking = 0
iPedestrian = 0
iOtherFailedtoYeild = 0
iDmgWhileParked = 0
iMiscComp = 0
iDrivLostControl = 0
iDrivFailClearance = 0
iHitRear = 0
iVehicleFailure = 0
iOther = 0

For Each rDescription In wsSheet.Range("V8:V100")

'Method or Data Member not Found
in line below
Select Case wsSheet.Value
Case "OTHER PARTY HIT REAR OF DRIVER"
iOtherParty = iOtherParty + 1
Case "PARKING/BACKING"
iParkingBacking = iParkingBacking + 1
Case "PEDESTRIAN COLLISION"
iPedestrian = iPedestrian + 1
Case "OTHER PARTY HIT REAR OF DRIVER"
iOtherFailedtoYeild = iOtherFailedtoYeild + 1
Case "DMG WHILE PARKED/OTH PARTY CUST"
iDmgWhileParked = iDmgWhileParked + 1
Case "MISC. COMPREHENSIVE"
iMiscComp = iMiscComp + 1
Case "DRIV LOST CONTROL OF VEHICLE"
iDrivLostControl = iDrivLostControl + 1
Case "DRIV FAILED TO OBSERVE CLEARANCE"
iDrivFailClearance = iDrivFailClearance + 1
Case "HIT REAR OF OTHER PARTY"
iHitRear = iHitRear + 1
Case "VEHICLE FAILURE"
iVehicleFailure = iVehicleFailure + 1
Case Else
iOther = iOther + 1
End Select

Next rDescription


iOther = MsgBox(iOther, vbOKOnly, "Accidents not in the Case
Statement:")
End Sub
----------------------------


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=381922