Solved: Need To Know How To: Reference a column Range in VB

[itechxxiv]

This most likely has a simple solution, but I?m stuck

:Here?s the Scenario:.
I have a Spreadsheet that we use at work to track numerous documents that are
interrelated to a large SAP project. Each document has to be tracked in terms of what
phase or status the document is currently in.

Within my Workbook I have 2 Worksheets: ?Worksheet1? for end-user data entry
?Worksheet2? is the database that stores, supplies, defines attributes and information to
?Worksheet1?.

~>List of phases a document will be tracked:_
? Not started =NS
? In-Progress =IP
? Under Review=UR
? Reviewed =R
? Approved =A
? On Hold =OH
? Cancelled =C

In the past we simply enter: "0"=NO or"1"=YES (entering this manually and at
times find that a document status has to many "1"'s in a particular row.)

All phases will be populated with a "0" & Phase set to ?NS? to begin with and the user will
enter/move "1" as the document progresses through each phase.

:Here?s what I want to happen:.
User selects an item in a drop list (7 items all together) and based on item selection
updates a range of cells that make up the 7 phases (Columns: K thru Q ). Each column
represents a phase.

:Here?s the Scope and Structure:.
~>Drop-down list Items & Structure:_
Define Name =currentPhaseList
Refers to: = Sheet2!$Y$6:$Y$12
Items that make up list:NS, IP, UR, RD, AP, OH, CD
1. Drop-down list is in ?Worksheet1? - column ?J8? (Column Header: [Document Phase])
2. Drop-down list is in ?Worksheet1? positioned in the cell range: ?9 thru 58? ($J9:$J58)

The Drop-down list resides in ?Worksheet1? ($J9:$J58)
~>Based on User list selection:_(NS, IP, UR, RD, AP, OH, CD)
will update COULMN & CELL range ($K9:$K58,$L9:&L58, $M9:$M58, $N9:$N58, $O9:$058, $P9:$P58,$Q9:$Q58)
+---+-----+-----+-----+-----+-----+-----+-----+-----+
|...|..J..|..K..|..L..|..M..|..N..|..O..|..P..|..Q..|
+---+-----+-----+-----+-----+-----+-----+-----+-----+
|.8.|.....|..NS.|..IP.|..UR.|..R..|..A..|..OH.|..C..|
+---+-----+-----+-----+-----+-----+-----+-----+-----+
|.9.|list.|..0..|..1..|..0..|..0..|..0..|..0..|..0..|
+---+-----+-----+-----+-----+-----+-----+-----+-----+
|.10|list.|..1..|..0..|..0..|..0..|..0..|..0..|..0..|
+---+-----+-----+-----+-----+-----+-----+-----+-----+
|.11|list.|..0..|..0..|..0..|..1..|..0..|..0..|..0..|
+---+-----+-----+-----+-----+-----+-----+-----+-----+
///////////////////////////
+---+-----+-----+-----+-----+-----+-----+-----+-----+
|.58|list.|..0..|..0..|..0..|..0..|..1..|..0..|..0..|
+---+-----+-----+-----+-----+-----+-----+-----+-----+
-----------------------------------------------------------------
Set phaseSt = ???How do I reference: Worksheet1? ($J9:$J58)????
===================================================================
??? Currently if set phaseSt = [J9] it works, but if try to define a column range it fails????
===================================================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim phaseSt As Range
set phaseSt = ??????????????????



If Not Intersect(Target, phaseSt) Is Nothing Then
Select Case phaseSt.Text
Case Is = "NS"
phaseSt.Offset(0, 1).Value = 1
phaseSt.Offset(0, 2).Value = 0
phaseSt.Offset(0, 3).Value = 0
phaseSt.Offset(0, 4).Value = 0
phaseSt.Offset(0, 5).Value = 0
phaseSt.Offset(0, 6).Value = 0
phaseSt.Offset(0, 7).Value = 0
phaseSt.Offset(0, 10).Value = "Not Started"
Case Is = "IP"
phaseSt.Offset(0, 1).Value = 0
phaseSt.Offset(0, 2).Value = 1
phaseSt.Offset(0, 3).Value = 0
phaseSt.Offset(0, 4).Value = 0
phaseSt.Offset(0, 5).Value = 0
phaseSt.Offset(0, 6).Value = 0
phaseSt.Offset(0, 7).Value = 0
phaseSt.Offset(0, 10).Value = "In-Progress"
Case Is = "UR"
phaseSt.Offset(0, 1).Value = 0
phaseSt.Offset(0, 2).Value = 0
phaseSt.Offset(0, 3).Value = 1
phaseSt.Offset(0, 4).Value = 0
phaseSt.Offset(0, 5).Value = 0
phaseSt.Offset(0, 6).Value = 0
phaseSt.Offset(0, 7).Value = 0
phaseSt.Offset(0, 10).Value = "Under Review"
Case Is = "RD"
phaseSt.Offset(0, 1).Value = 0
phaseSt.Offset(0, 2).Value = 0
phaseSt.Offset(0, 3).Value = 0
phaseSt.Offset(0, 4).Value = 1
phaseSt.Offset(0, 5).Value = 0
phaseSt.Offset(0, 6).Value = 0
phaseSt.Offset(0, 7).Value = 0
phaseSt.Offset(0, 10).Value = "Reviewed"
Case Is = "AP"
phaseSt.Offset(0, 1).Value = 0
phaseSt.Offset(0, 2).Value = 0
phaseSt.Offset(0, 3).Value = 0
phaseSt.Offset(0, 4).Value = 0
phaseSt.Offset(0, 5).Value = 1
phaseSt.Offset(0, 6).Value = 0
phaseSt.Offset(0, 7).Value = 0
phaseSt.Offset(0, 10).Value = "Approved"
Case Is = "OH"
phaseSt.Offset(0, 1).Value = 0
phaseSt.Offset(0, 2).Value = 0
phaseSt.Offset(0, 3).Value = 0
phaseSt.Offset(0, 4).Value = 0
phaseSt.Offset(0, 5).Value = 0
phaseSt.Offset(0, 6).Value = 1
phaseSt.Offset(0, 7).Value = 0
phaseSt.Offset(0, 10).Value = "On Hold"
Case Is = "CD"
phaseSt.Offset(0, 1).Value = 0
phaseSt.Offset(0, 2).Value = 0
phaseSt.Offset(0, 3).Value =
phaseSt.Offset(0, 4).Value = 0
phaseSt.Offset(0, 5).Value = 0
phaseSt.Offset(0, 6).Value = 0
phaseSt.Offset(0, 7).Value = 1
phaseSt.Offset(0, 10).Value = "Cancelled"
Case Else
phaseSt.Offset(0, 1).Value = 0
End Select
End If
End Sub
'=========================
I have worked all weekend developing this report. Up until now things were going good.

THIS DUE TOMORROW!!!! Can anyone help ASAP (it?s now 7:32PM EST ? 12/4/05)

[mdmackillop]

A slight mix up between the Target and the range
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim phaseSt As Range
Set phaseSt = Range("J9:J58")

If Not Intersect(Target, phaseSt) Is Nothing Then
Select Case Target.Text
Case Is = "NS"
Target.Offset(0, 1).Value = 1
Target.Offset(0, 2).Value = 0
Target.Offset(0, 3).Value = 0
Target.Offset(0, 4).Value = 0
Target.Offset(0, 5).Value = 0
Target.Offset(0, 6).Value = 0
Target.Offset(0, 7).Value = 0
Target.Offset(0, 10).Value = "Not Started"
Case Is = "IP"
Target.Offset(0, 1).Value = 0
Target.Offset(0, 2).Value = 1
Target.Offset(0, 3).Value = 0
Target.Offset(0, 4).Value = 0
Target.Offset(0, 5).Value = 0
Target.Offset(0, 6).Value = 0
Target.Offset(0, 7).Value = 0
Target.Offset(0, 10).Value = "In-Progress"
Case Is = "UR"
Target.Offset(0, 1).Value = 0
Target.Offset(0, 2).Value = 0
Target.Offset(0, 3).Value = 1
Target.Offset(0, 4).Value = 0
Target.Offset(0, 5).Value = 0
Target.Offset(0, 6).Value = 0
Target.Offset(0, 7).Value = 0
Target.Offset(0, 10).Value = "Under Review"
Case Is = "RD"
Target.Offset(0, 1).Value = 0
Target.Offset(0, 2).Value = 0
Target.Offset(0, 3).Value = 0
Target.Offset(0, 4).Value = 1
Target.Offset(0, 5).Value = 0
Target.Offset(0, 6).Value = 0
Target.Offset(0, 7).Value = 0
Target.Offset(0, 10).Value = "Reviewed"
Case Is = "AP"
Target.Offset(0, 1).Value = 0
Target.Offset(0, 2).Value = 0
Target.Offset(0, 3).Value = 0
Target.Offset(0, 4).Value = 0
Target.Offset(0, 5).Value = 1
Target.Offset(0, 6).Value = 0
Target.Offset(0, 7).Value = 0
Target.Offset(0, 10).Value = "Approved"
Case Is = "OH"
Target.Offset(0, 1).Value = 0
Target.Offset(0, 2).Value = 0
Target.Offset(0, 3).Value = 0
Target.Offset(0, 4).Value = 0
Target.Offset(0, 5).Value = 0
Target.Offset(0, 6).Value = 1
Target.Offset(0, 7).Value = 0
Target.Offset(0, 10).Value = "On Hold"
Case Is = "CD"
Target.Offset(0, 1).Value = 0
Target.Offset(0, 2).Value = 0
Target.Offset(0, 3).Value = 0
Target.Offset(0, 4).Value = 0
Target.Offset(0, 5).Value = 0
Target.Offset(0, 6).Value = 0
Target.Offset(0, 7).Value = 1
Target.Offset(0, 10).Value = "Cancelled"
Case Else
Target.Offset(0, 1).Value = 0
End Select
End If
End Sub
[/VBA]


or a bit more economically

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim phaseSt As Range
Set phaseSt = Range("J9:J58")
If Not Intersect(Target, phaseSt) Is Nothing Then
Target.Range("B1:H1") = 0
Select Case Target.Text
Case Is = "NS"
Target.Offset(0, 1).Value = 1
Target.Offset(0, 10).Value = "Not Started"
Case Is = "IP"
Target.Offset(0, 2).Value = 1
Target.Offset(0, 10).Value = "In-Progress"
Case Is = "UR"
Target.Offset(0, 3).Value = 1
Target.Offset(0, 10).Value = "Under Review"
Case Is = "RD"
Target.Offset(0, 4).Value = 1
Target.Offset(0, 10).Value = "Reviewed"
Case Is = "AP"
Target.Offset(0, 5).Value = 1
Target.Offset(0, 10).Value = "Approved"
Case Is = "OH"
Target.Offset(0, 6).Value = 1
Target.Offset(0, 10).Value = "On Hold"
Case Is = "CD"
Target.Offset(0, 7).Value = 1
Target.Offset(0, 10).Value = "Cancelled"
Case Else
Target.Range("B1:H1").ClearContents
Target.Offset(0, 1).Value = 0
End Select
End If
End Sub

[/VBA]