Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2016
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Runtime Error 13 for a simple for loop

    Good folks (love the board by the by) I have a short and simple sub that is giving me fits. I have tried renaming the variables and changing what type they are declared as...all to no avail... Please inspect the following and advise,

    Code:
    Sub CHEMStatusExpiry()
    
    Dim Wr As Integer
    Dim LWr As Integer
    
    
    LWr = Application.WorksheetFunction.CountA(Sheet6.Range("a4:a800"))
    
    
    For Wr = 4 To LWr
        If Sheet6.Cells(Wr, 19).Value = "Ce" Then
        Sheet6.Cells(Wr, 19).Value = "C"
        End If
    Next Wr
    End Sub
    Thanks in advance for your assistance (promise not to laugh if this is simple)
    Last edited by primevyl; 2016-07-22 at 14:29. Reason: Added Code Tags

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Primevyl,

    Welcome to the Lounge as a New Poster!

    Let's say for instance that you have values in A4:A10 & A20:A25

    The Count A function will return 13!

    So now you loop will run from 4 to 13!

    I assume what you're trying to do is avoid processing blank entries but this isn't going to get you what you want.

    Try this:
    Code:
    Sub CHESMStatusExpiry()
    
      Dim Wr  As Integer
      Dim LWr As Integer
      Dim sht as Worksheet
    
      Set sht = ActiveWorkbook.Sheets("Sheet6")
    
      LWr = sht.Range("a4:a800").rows.count + 3
    
    
      For Wr = 4 To LWr
      
         If sht.Cells(Wr, 19).Value = "Ce" Then
           sht.Cells(Wr, 19).Value = "C"
         End If
    	 
      Next Wr
      
    End Sub
    Yes, you'll process all the rows but then again you won't miss any either!

    Now if all your values are contiguous (no blank lines) then you could use:
    LWr = Application.WorksheetFunction.CountA(sht.Range("a4 :a800")) + 3

    HTH
    Last edited by RetiredGeek; 2016-07-21 at 21:22.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    primevyl (2016-07-21)

  4. #3
    New Lounger
    Join Date
    Jul 2016
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts
    RG

    Thanks for the reply...i pasted the wrong version of this (sans the comments) and so let me explain what i am trying to accomplish here.

    Sub CHEMStatusExpiry()

    Dim Wr As Integer
    Dim LWr As Integer


    LWr = Application.WorksheetFunction.CountA(Sheet6.Range( "a4:a800")) 'I'm using Counta to limit the loop to only occupied rows on the table on sheet 6


    For Wr = 4 To LWr
    If Sheet6.Cells(Wr, 19).Value = "Ce" Then 'I only want the loop to find the cells with "Ce" in them...
    Sheet6.Cells(Wr, 19).Value = "C" 'to change them into "C"
    End If
    Next Wr
    End Sub

    I am going to give your code a shot tonight...i just wanted to clarify the code's intent to you since i posted the wrong version originally...Thanks again!
    Last edited by primevyl; 2016-07-22 at 14:30.

  5. #4
    Lounger
    Join Date
    May 2003
    Posts
    29
    Thanks
    3
    Thanked 1 Time in 1 Post
    Quote Originally Posted by primevyl View Post
    RG

    Thanks for the reply...i pasted the wrong version of this (sans the comments) and so let me explain what i am trying to accomplish here.

    Sub CHEMStatusExpiry()

    Dim Wr As Integer
    Dim LWr As Integer


    LWr = Application.WorksheetFunction.CountA(Sheet6.Range( "a4:a800")) 'I'm using Counta to limit the loop to only occupied rows on the table on sheet 6


    For Wr = 4 To LWr
    If Sheet6.Cells(Wr, 19).Value = "Ce" Then 'I only want the loop to find the cells with "Ce" in them...
    Sheet6.Cells(Wr, 19).Value = "C" 'to change them into "C"
    End If
    Next Wr
    End Sub

    I am going to give your code a shot tonight...i just wanted to clarify the code's intent to you since i posted the wrong version originally...Thanks again!
    Problem is, your test counts occupied rows, but the loop does rows 4 to whatever your count was - which isn't the same necessarily - I'd use

    Dim cl As Variant
    For Each cl In Sheets("Sheet6").Range("S4:S" & Sheets("Sheet6").Cells.SpecialCells(xlCellTypeLast Cell).Row)
    If cl.Value = "Ce" Then cl.Value = "C"
    Next

  6. The Following User Says Thank You to aidan100 For This Useful Post:

    primevyl (2016-07-25)

Tags for this Thread

Posting Permissions

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