locked
What is the problem of my code? (Macro) RRS feed

  • Question

  • Hi there,

    I have the following code and I have tried many times to amend it but its still not working. Could anyone please help take a look at it? Thanks in advance!!

    Dim i As Integer
        For i = 2 To m_TL + 1
            If sourcesheet.Range("B" & i) > 600000 Then
                sourcesheet.Range("F" & i).Value = "NOT Trade Loan"
            End If
        Next i

    destbookn.Save

    sourcesheet.Activate
    Dim j As Integer
        For j = 2 To m_TL + 1
            If sourcesheet.Range("F" & j) = "#N/A" Then   <---------The problem appears here!
                If sourcesheet.Range("H" & j) = "BQ" Then
                    sourcesheet.Range("F" & j).Value = "=VLOOKUP(AR" & j & ",'" & sformula_TL10 & "'!$B:$E,4,FALSE)"
                End If
            End If
        Next j

    The system says Run-time error '13': Type mismatch.
    Thanks!

    Friday, July 6, 2012 9:01 AM

Answers

  • Are you looking for cells that contain the error value #N/A? That is not a string. You can test for the error value as follows:

            If sourcesheet.Range("F" & j) = CVErr(xlErrNA) Then

    The CVErr function returns an error value that can be used to check whether a cell contains a specific error. If you want to test whether the cell contains ANY error without specifying what kind, you can use

            If IsError(sourcesheet.Range("F" & j)) Then


    Regards, Hans Vogelaar

    Friday, July 6, 2012 9:21 AM
  • If you want to test for a specific error type the need to first test that an error exists and then test for the specific error. Note that you can use the error name enclosed in square brackets for the test.

    Sub TestSpecificError()
       
        Dim SourceSheet As Worksheet
        Dim j
        j = 6
       
        Set SourceSheet = Sheets("Sheet1")
       
        If IsError(SourceSheet.Range("F" & j)) Then
            Select Case CVErr(SourceSheet.Range("F" & j))
            'Select Case SourceSheet.Range("F" & j)  'Alternative code added with edit after original post
                Case [#DIV/0!]
                    MsgBox "Error #DIV/0!"
                Case [#N/A]
                    MsgBox "Error #N/A"
                Case [#NAME?]
                    MsgBox "Error #NAME?"
                Case [#NULL!]
                    MsgBox "Error #NULL!"
                Case [#NUM!]
                    MsgBox "Error #NUM!"
                Case [#REF!]
                    MsgBox "Error #REF!"
                Case [#VALUE!]
                    MsgBox "Error #VALUE!"
                Case Else
                    MsgBox "Error not identified"
            End Select
       
        Else
                MsgBox "Does NOT Contain error"
        End If
       
    End Sub

    Following also works if looking specifically for #N/A and the VBA code does not error if no error exists in cell.

    If WorksheetFunction.IsNA(SourceSheet.Range("F" & j)) Then


    Regards, OssieMac




    • Edited by OssieMac Saturday, July 7, 2012 11:15 AM
    • Proposed as answer by VBAToolsMVP, Editor Saturday, July 7, 2012 12:39 PM
    • Marked as answer by Jackynck Monday, July 9, 2012 1:04 AM
    Saturday, July 7, 2012 8:42 AM

All replies

  • Are you looking for cells that contain the error value #N/A? That is not a string. You can test for the error value as follows:

            If sourcesheet.Range("F" & j) = CVErr(xlErrNA) Then

    The CVErr function returns an error value that can be used to check whether a cell contains a specific error. If you want to test whether the cell contains ANY error without specifying what kind, you can use

            If IsError(sourcesheet.Range("F" & j)) Then


    Regards, Hans Vogelaar

    Friday, July 6, 2012 9:21 AM
  • The following will return a VBA code error if the cell does not contain #N/A

    If sourcesheet.Range("F" & j) = CVErr(xlErrNA) Then

    the following appears to work correctly.

    If IsError(sourcesheet.Range("F" & j)) Then


    Regards, OssieMac


    • Edited by OssieMac Saturday, July 7, 2012 6:39 AM
    Saturday, July 7, 2012 6:39 AM
  • If you want to test for a specific error type the need to first test that an error exists and then test for the specific error. Note that you can use the error name enclosed in square brackets for the test.

    Sub TestSpecificError()
       
        Dim SourceSheet As Worksheet
        Dim j
        j = 6
       
        Set SourceSheet = Sheets("Sheet1")
       
        If IsError(SourceSheet.Range("F" & j)) Then
            Select Case CVErr(SourceSheet.Range("F" & j))
            'Select Case SourceSheet.Range("F" & j)  'Alternative code added with edit after original post
                Case [#DIV/0!]
                    MsgBox "Error #DIV/0!"
                Case [#N/A]
                    MsgBox "Error #N/A"
                Case [#NAME?]
                    MsgBox "Error #NAME?"
                Case [#NULL!]
                    MsgBox "Error #NULL!"
                Case [#NUM!]
                    MsgBox "Error #NUM!"
                Case [#REF!]
                    MsgBox "Error #REF!"
                Case [#VALUE!]
                    MsgBox "Error #VALUE!"
                Case Else
                    MsgBox "Error not identified"
            End Select
       
        Else
                MsgBox "Does NOT Contain error"
        End If
       
    End Sub

    Following also works if looking specifically for #N/A and the VBA code does not error if no error exists in cell.

    If WorksheetFunction.IsNA(SourceSheet.Range("F" & j)) Then


    Regards, OssieMac




    • Edited by OssieMac Saturday, July 7, 2012 11:15 AM
    • Proposed as answer by VBAToolsMVP, Editor Saturday, July 7, 2012 12:39 PM
    • Marked as answer by Jackynck Monday, July 9, 2012 1:04 AM
    Saturday, July 7, 2012 8:42 AM
  • Thanks Hans and OssieMac!
    I work that out now!
    Monday, July 9, 2012 1:06 AM