I am trying to loop through every 92 line section of my excel worksheet and on the 20th line of each section alter a rate based on the following IF statements below. Issue I am having is that it will evaluate one 92 line section and update data but then it ends sub. I have this code working to loop through for other things but my IF statement must be stopping it from moving to the next section. Any help/suggestions are very much appreciated!
Sub testLotAllocationRate()
On Error GoTo ErrorHandler1
Dim rg As Range
Dim Counter As Integer
templateRow = 92
'''Every 92nd line need to look at and evaluate that particular cell for the IF statement below
templateBegin = 110
Set rowrg = ActiveWorkbook.Worksheets("Projects").Range("Endrow")
'Endrow = last row with data
endrange = rowrg.Row
ProjectCount = ((endrange - templateBegin) / templateRow)
If ProjectCount >= 1 Then
For projectcounter = ProjectCount - 1 To 0 Step -1
Row = (projectcounter * templateRow) + templateBegin + 20
'''Row ='s every 92nd line and dropping down 20 lines from the top of that section
Set rg = Worksheets("Projects").Range("H" & Row)
If rg.FormulaR1C1 = "0.01" Then
rg.FormulaR1C1 = ".02"
rg.NumberFormat = "0.0%"
ElseIf rg.FormulaR1C1 = "0.015" Then
rg.FormulaR1C1 = ".01"
rg.NumberFormat = "0.0%"
ElseIf rg.FormulaR1C1 = "0.02" Then
rg.FormulaR1C1 = ".03"
rg.NumberFormat = "0.0%"
Else: rg.FormulaR1C1 = "0.0265"
rg.FormulaR1C1 = ".03"
rg.NumberFormat = "0.0%"
End If
Next projectcounter
End If
Set rg = Nothing
ErrorHandler1:
End Sub
Sub testLotAllocationRate()
On Error GoTo ErrorHandler1
Dim rg As Range
Dim Counter As Integer
templateRow = 92
'''Every 92nd line need to look at and evaluate that particular cell for the IF statement below
templateBegin = 110
Set rowrg = ActiveWorkbook.Worksheets("Projects").Range("Endrow")
'Endrow = last row with data
endrange = rowrg.Row
ProjectCount = ((endrange - templateBegin) / templateRow)
If ProjectCount >= 1 Then
For projectcounter = ProjectCount - 1 To 0 Step -1
Row = (projectcounter * templateRow) + templateBegin + 20
'''Row ='s every 92nd line and dropping down 20 lines from the top of that section
Set rg = Worksheets("Projects").Range("H" & Row)
If rg.FormulaR1C1 = "0.01" Then
rg.FormulaR1C1 = ".02"
rg.NumberFormat = "0.0%"
ElseIf rg.FormulaR1C1 = "0.015" Then
rg.FormulaR1C1 = ".01"
rg.NumberFormat = "0.0%"
ElseIf rg.FormulaR1C1 = "0.02" Then
rg.FormulaR1C1 = ".03"
rg.NumberFormat = "0.0%"
Else: rg.FormulaR1C1 = "0.0265"
rg.FormulaR1C1 = ".03"
rg.NumberFormat = "0.0%"
End If
Next projectcounter
End If
Set rg = Nothing
ErrorHandler1:
End Sub