Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Looping Code using IF statements in VBA

  1. #1

    Looping Code using IF statements in VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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.

  3. #3
    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!!
    Attached Files Attached Files

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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?

  5. #5
    Quote Originally Posted by JeffreyWeir View Post
    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

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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 rate New 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.

  7. #7
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Quote Originally Posted by eeder1 View Post
    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?

  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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

  9. #9
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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

  10. #10
    Quote Originally Posted by JeffreyWeir View Post
    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.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •