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

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

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