+ Reply to Thread
Results 1 to 5 of 5

Dealing with error when looping through a range

Hybrid View

  1. #1
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Dealing with error when looping through a range

    Hi,
    Anyone got an idea how to overcome this problem.
    if looping through a range, one of the cells has #N/A it causes a "Run Time Error 13 Type Mismatch error
    usually the range will have numbers in them.
    How do i get the loop to go to next cell?

    Loop()
    Dim c, Myrng As Range
    Dim usedrng As String
    Dim x As Long
    
    
    usedrng = "V6:V120"
    Set Myrng = Sheet5.Range(usedrng)
    x = 0
    ''''''errors here
    For Each c In Myrng
        If c.Value = 1 Then
        x = x + 1
        End If
    Next
    MsgBox x
    End Sub
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Dealing with error when looping through a range

    For Each c In Myrng
        If not iserror(c.value)then
    If c.Value = 1 Then
        x = x + 1
        End If
    End if
    Next
    I reckon countif would be more efficient than looping though
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Dealing with error when looping through a range

    You can include an error trapping
    On error resume next

    Or you could inlcude one of thes line in your code.

    If c.Value = <> "#N/A"Then
    If c.text = <> "#N/A"Then

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Dealing with error when looping through a range

    Try this.
    Option Explicit
    
    Sub TheLoop()
    Dim c, Myrng As Range
    Dim usedrng As String
    Dim x As Long
    
        usedrng = "V6:V120"
        Set Myrng = Sheet5.Range(usedrng)
    
        For Each c In Myrng
            If Not IsError(c) Then
    
                x = x - (c.Value = 1)
            End If
        Next
        
        MsgBox x
        
    End Sub
    If posting code please use code tags, see here.

  5. #5
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Dealing with error when looping through a range

    Thanks all.
    That done the trick.
    Had amoment of blindness then.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1