viomman
03-04-2009, 08:55 AM
I am traying to loop through a range find a specific test ofset by on then use that value for another subroutine. I am getting a compile error at the Set YrVal = ActiveCell.Value line.
Can any one help me with this problem?
here is my subroutine
Sub ResetFormulasCalc()
Dim rBcells As Range, rBLoopCells As Range
Dim OMRCRange As Range
Dim PctDoneB As Single
Dim CounterB As Integer
Dim RowMaxB As Integer, ColMaxB As Integer
Dim YrVal As Long
Application.ScreenUpdating = False
Sheets("NonAnnCalc").Select
Range("A1").Select
Set rBcells = Sheets("NonAnnCalc").Range("F11:CZ11")
Set OMRCRange = Sheets("NonAnnCalc").Range("F26:CZ44")
rBcells.Select
CounterB = 10
RowMaxB = 100
ColMaxB = 25
On Error Resume Next
For Each rBLoopCells In rBcells
If rBLoopCells.Value > 0 Then
With rBLoopCells
ActiveCell.Select
.Offset(-1, 0).Select
ActiveCell.Select
Set YrVal = ActiveCell.Value
Call ResetFound
End With
End If
CounterB = CounterB + 25
PctDoneB = CounterB / (RowMax * ColMax)
With ReformProgCalc
.FrameProgressCalc.Caption = Format(PctDoneB * 100)
.LabelProgressCalc.Width = PctDoneB * (.FrameProgressCalc.Width - 10)
End With
Next rBLoopCells
DoEvents
Unload ReformProgCalc
Sheets("NonAnnCalc").Columns("DA:IU").ClearContents
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets("LCCAParameters").Select
Range("A1").Select
End Sub
Can any one help me with this problem?
here is my subroutine
Sub ResetFormulasCalc()
Dim rBcells As Range, rBLoopCells As Range
Dim OMRCRange As Range
Dim PctDoneB As Single
Dim CounterB As Integer
Dim RowMaxB As Integer, ColMaxB As Integer
Dim YrVal As Long
Application.ScreenUpdating = False
Sheets("NonAnnCalc").Select
Range("A1").Select
Set rBcells = Sheets("NonAnnCalc").Range("F11:CZ11")
Set OMRCRange = Sheets("NonAnnCalc").Range("F26:CZ44")
rBcells.Select
CounterB = 10
RowMaxB = 100
ColMaxB = 25
On Error Resume Next
For Each rBLoopCells In rBcells
If rBLoopCells.Value > 0 Then
With rBLoopCells
ActiveCell.Select
.Offset(-1, 0).Select
ActiveCell.Select
Set YrVal = ActiveCell.Value
Call ResetFound
End With
End If
CounterB = CounterB + 25
PctDoneB = CounterB / (RowMax * ColMax)
With ReformProgCalc
.FrameProgressCalc.Caption = Format(PctDoneB * 100)
.LabelProgressCalc.Width = PctDoneB * (.FrameProgressCalc.Width - 10)
End With
Next rBLoopCells
DoEvents
Unload ReformProgCalc
Sheets("NonAnnCalc").Columns("DA:IU").ClearContents
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets("LCCAParameters").Select
Range("A1").Select
End Sub