Looping Code using IF statements in VBA

eeder1

New member
Joined
Jun 10, 2013
Messages
4
Reaction score
0
Points
0
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
 
Hi eeder1. It would be easier to conceptualize your issue and trouble shoot your code if you could upload a sample spreadsheet with dummy data (so it's non-confidential) , and make some notes in it as to what you need done. That way, we could step through the code and see what it's actually doing to your sample data. Much easier than trying to conceptualize this in my head.
 
What I am trying to accomplish is to update the "TestRate" which occurs every 92 lines so starting in cell H130 then H222, then H314, then H406 etc etc. per the If statement in the code but my code is not looping through each 92 line section just one section and then ending the sub. I have attached a spreadsheet that should work for example purposes. Thanks for looking!!
 

Attachments

  • TestRates.xlsx
    452.3 KB · Views: 16
Hi again.
You have a "On Error GoTo ErrorHandler1" instruction in your code, which is a bit dangerous because this masks any actual problems that are in the code.
So what's happening is that your code refers to a named range "Endrow" in this line:
Set rowrg = ActiveWorkbook.Worksheets("Projects").Range("Endrow")
...but there is no such named range in the workbook, so it throws an error, then goes straght to ErrorHandler1 and then exits as per the On Error GoTo ErrorHandler1 instruction.

Is there supposed to be a named range called "Endrow" in your sheet?
 
Hi again.
You have a "On Error GoTo ErrorHandler1" instruction in your code, which is a bit dangerous because this masks any actual problems that are in the code.
So what's happening is that your code refers to a named range "Endrow" in this line:
Set rowrg = ActiveWorkbook.Worksheets("Projects").Range("Endrow")
...but there is no such named range in the workbook, so it throws an error, then goes straght to ErrorHandler1 and then exits as per the On Error GoTo ErrorHandler1 instruction.

Is there supposed to be a named range called "Endrow" in your sheet?
Yes, "endrow" should be named and position itself after the last row with data. If it is not in there must ha e been inadvertently deleted when trying to create the confidential file. I know range is not the issue. Thanks again
 
Okay, looking at your code, it appears that you want to replace the 'Old rate' with the 'New Rate' in column H of your spreadsheet for every cell denoted 'TestRate' in column G, based on the table below.

Old rateNew rate
1.00%2.00%
1.50%1.00%
2.00%3.00%
2.65%3.00%

(Note: without knowing your intent, it seems strange that you want to change 1.00% up to 2.00% while also changing 1.5% down to 1.00%...are you sure that's what you want to do?
Also, the only values of TestRate in the sample you sent me are 1%, 1.5%, 2% and 4%. So in this sample, your code will ignore the 4% Testrate, and the bit concerning Testrate of 2.65% will never get called).

So an alternative approach to counting rows would be to scan column G for the word "TestRate", then make the desired change to column H. I'll have a play.
 
Yes, "endrow" should be named and position itself after the last row with data. If it is not in there must ha e been inadvertently deleted when trying to create the confidential file. I know range is not the issue. Thanks again
So just so I'm running your code correctly, can you let me know what row 'Endrow" should be in relation to your sample data?
 
Here's how I would code this up:

Code:
Sub jeff()
Dim rng As Range
Dim cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Range("G1").EntireColumn) 'This makes sure we're not checking beyond the actual used range
Set rng = rng.SpecialCells(xlCellTypeConstants) 'This makes sure we don't waste time checking blank cells, by effectively filtering our range to include only constants
For Each cell In rng
    If cell.Value2 = "TestRate" Then
        Select Case cell.Offset(, 1).Value2
            Case 0.01: cell.Offset(, 1).Value2 = 0.02
            Case 0.015: cell.Offset(, 1).Value2 = 0.01
            Case 0.02: cell.Offset(, 1).Value2 = 0.03
            Case 0.0265: cell.Offset(, 1).Value2 = 0.03
        End Select
    End If
Next cell
End Sub
 
I'm still happy to show you where your code is failing...you just need to give me the address of "endrow" for the sample dataset
 
I'm still happy to show you where your code is failing...you just need to give me the address of "endrow" for the sample dataset

Your code worked perfectly!! But I still think it would be handy to know where my code was failing so the "EndRow" range should be in cell C1858. Using column G was a great idea but there will still be instances where I will not have a column to refer to as an identifier like "TestRate".

Thanks for your time and if you can figure out or enhance my original code that would be much appreciated.
 
Well, with that endrow in there your code works fine.

Here's what I suggest you do.

  1. Comment out the line On Error GoTo ErrorHandler1 so that if something goes wrong we'll know what the problem is.
  2. Just so that we can see what is happenning, TEMPORARILY add the line rg.select after the line Set rg = Worksheets("Projects").Range("H" & Row)
  3. Resize the VBA window so it takes up half the screen, and resize the Excel window so it takes up the other half
  4. Step though your code line by line by putting the cursor on the first line, then repeatedly pushing F8. This will highlight each line in turn in yellow, and each time you push F8 it executes the currently highlighted line of code.

This should let you see what's going on, and the temporary addition of the rg.select bit will let you see whether the correct cell is being selected.

Give that a go, and let me know what happens.
 
Back
Top