Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Troube defining variable
I am stumped on what should be a simple problem - defining a variable through
the use of range(cells()). I have copied my code below and noted where I am running into trouble. I get a value for cells(4,Wy) shown in the debug mode. But the value for Quarter is shown as empty. I am puzzled as to what syntax issue might be tripping me up. Thanks in advance for your help... Sub RiskWF() Dim REnd As Variant Dim Rx As Variant 'Row counter in register Dim Ry As Variant 'Column counter in register Dim Wx As Variant 'Row counter in waterfall Dim Wy As Variant 'Column counter in register Dim Today As Variant Dim Quarter As Variant Dim Red As Variant Dim Yellow As Variant Dim Green As Variant Dim RiskInterest As Variant Dim RiskOwner As Variant Dim RiskScore As Variant Dim RiskMitComplete As Variant Dim RiskExpired As Variant Worksheets("RiskWaterFall").Activate REnd = Worksheets("REGISTER").range("A65000").End(xlUp).R ow RiskInterest = Mid(range("A2"), 1, 1) Today = range("D2") For Wy = 2 To 16 Red = 0 Yellow = 0 Green = 0 Quarter = ActiveSheet.range(Cells(4, Wy)) <- TROUBLE HERE For Rx = 4 To REnd 'Read in variables from row RiskOwner = Mid(Worksheets("REGISTER").range(Cells(Rx, 1)), 1, 1) RiskMitComplete = Worksheets("REGISTER").range(Cells(Rx, 58)) RiskExpired = Worksheets("REGISTER").range(Cells(Rx, 48)) 'Choose appropriate risk score If RiskMitComplete = Today And RiskExpired Today Then RiskScore = 0 ElseIf RiskExpired Today Then RiskScore = 0 ElseIf RiskMitComplete = Today Then RiskScore = Worksheets("REGISTER").range(Cells(Rx, 37)) Else RiskScore = Worksheets("REGISTER").range(Cells(Rx, 21)) End If 'Incremental appropriate risk counter If RiskScore = 20 Then Red = Red + 1 ElseIf RiskScore = 6 Then Yellow = Yellow + 1 ElseIf RiskScore = 1 Then Green = Green + 1 End If Next Rx 'Put counters in appropriate cells range(Cells(5, Wy)) = Red range(Cells(6, Wy)) = Yellow range(Cells(7, Wy)) = Green Next Wy End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Troube defining variable
Take the Range bit off:
Quarter = ActiveSheet.Cells(4, Wy).Value '<- TROUBLE HERE Same at the end of the code. Also better not to use Variant datatype for all, but instead use Long where you can. RBS "Mic" wrote in message ... I am stumped on what should be a simple problem - defining a variable through the use of range(cells()). I have copied my code below and noted where I am running into trouble. I get a value for cells(4,Wy) shown in the debug mode. But the value for Quarter is shown as empty. I am puzzled as to what syntax issue might be tripping me up. Thanks in advance for your help... Sub RiskWF() Dim REnd As Variant Dim Rx As Variant 'Row counter in register Dim Ry As Variant 'Column counter in register Dim Wx As Variant 'Row counter in waterfall Dim Wy As Variant 'Column counter in register Dim Today As Variant Dim Quarter As Variant Dim Red As Variant Dim Yellow As Variant Dim Green As Variant Dim RiskInterest As Variant Dim RiskOwner As Variant Dim RiskScore As Variant Dim RiskMitComplete As Variant Dim RiskExpired As Variant Worksheets("RiskWaterFall").Activate REnd = Worksheets("REGISTER").range("A65000").End(xlUp).R ow RiskInterest = Mid(range("A2"), 1, 1) Today = range("D2") For Wy = 2 To 16 Red = 0 Yellow = 0 Green = 0 Quarter = ActiveSheet.range(Cells(4, Wy)) <- TROUBLE HERE For Rx = 4 To REnd 'Read in variables from row RiskOwner = Mid(Worksheets("REGISTER").range(Cells(Rx, 1)), 1, 1) RiskMitComplete = Worksheets("REGISTER").range(Cells(Rx, 58)) RiskExpired = Worksheets("REGISTER").range(Cells(Rx, 48)) 'Choose appropriate risk score If RiskMitComplete = Today And RiskExpired Today Then RiskScore = 0 ElseIf RiskExpired Today Then RiskScore = 0 ElseIf RiskMitComplete = Today Then RiskScore = Worksheets("REGISTER").range(Cells(Rx, 37)) Else RiskScore = Worksheets("REGISTER").range(Cells(Rx, 21)) End If 'Incremental appropriate risk counter If RiskScore = 20 Then Red = Red + 1 ElseIf RiskScore = 6 Then Yellow = Yellow + 1 ElseIf RiskScore = 1 Then Green = Green + 1 End If Next Rx 'Put counters in appropriate cells range(Cells(5, Wy)) = Red range(Cells(6, Wy)) = Yellow range(Cells(7, Wy)) = Green Next Wy End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Troube defining variable
Thanks much - that works!
"RB Smissaert" wrote: Take the Range bit off: Quarter = ActiveSheet.Cells(4, Wy).Value '<- TROUBLE HERE Same at the end of the code. Also better not to use Variant datatype for all, but instead use Long where you can. RBS "Mic" wrote in message ... I am stumped on what should be a simple problem - defining a variable through the use of range(cells()). I have copied my code below and noted where I am running into trouble. I get a value for cells(4,Wy) shown in the debug mode. But the value for Quarter is shown as empty. I am puzzled as to what syntax issue might be tripping me up. Thanks in advance for your help... Sub RiskWF() Dim REnd As Variant Dim Rx As Variant 'Row counter in register Dim Ry As Variant 'Column counter in register Dim Wx As Variant 'Row counter in waterfall Dim Wy As Variant 'Column counter in register Dim Today As Variant Dim Quarter As Variant Dim Red As Variant Dim Yellow As Variant Dim Green As Variant Dim RiskInterest As Variant Dim RiskOwner As Variant Dim RiskScore As Variant Dim RiskMitComplete As Variant Dim RiskExpired As Variant Worksheets("RiskWaterFall").Activate REnd = Worksheets("REGISTER").range("A65000").End(xlUp).R ow RiskInterest = Mid(range("A2"), 1, 1) Today = range("D2") For Wy = 2 To 16 Red = 0 Yellow = 0 Green = 0 Quarter = ActiveSheet.range(Cells(4, Wy)) <- TROUBLE HERE For Rx = 4 To REnd 'Read in variables from row RiskOwner = Mid(Worksheets("REGISTER").range(Cells(Rx, 1)), 1, 1) RiskMitComplete = Worksheets("REGISTER").range(Cells(Rx, 58)) RiskExpired = Worksheets("REGISTER").range(Cells(Rx, 48)) 'Choose appropriate risk score If RiskMitComplete = Today And RiskExpired Today Then RiskScore = 0 ElseIf RiskExpired Today Then RiskScore = 0 ElseIf RiskMitComplete = Today Then RiskScore = Worksheets("REGISTER").range(Cells(Rx, 37)) Else RiskScore = Worksheets("REGISTER").range(Cells(Rx, 21)) End If 'Incremental appropriate risk counter If RiskScore = 20 Then Red = Red + 1 ElseIf RiskScore = 6 Then Yellow = Yellow + 1 ElseIf RiskScore = 1 Then Green = Green + 1 End If Next Rx 'Put counters in appropriate cells range(Cells(5, Wy)) = Red range(Cells(6, Wy)) = Yellow range(Cells(7, Wy)) = Green Next Wy End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining a Variable | Excel Programming | |||
Defining a variable within a sub... | Excel Programming | |||
Defining a variable within a sub... | Excel Programming | |||
Defining a variable within a sub... | Excel Programming | |||
Defining a Variable | Excel Programming |