+ Reply to Thread
Results 1 to 3 of 3

Runtime Error '13': Type mismatch

  1. #1
    Linking to specific cells in pivot table
    Guest

    Runtime Error '13': Type mismatch

    Hi - as I am developing a spreadsheet that I am working on, I am saving new
    versions with every somewhat significant change I make to the spreadsheet.
    For some reason, the ALMOST identical code works in one version of the
    spreadsheet, but not in the other. In the scenario it is not working, I am
    getting the error "Runtime Error '13': Type mismatch". Below is the code
    for both scenarios:

    - Scenario 1 (code works fine)

    Sub test1()

    Dim A
    Dim B
    Dim C
    Dim testvalue
    Dim range1 As range

    Sheets("Frequency Input").Select
    range("A1").Select
    range(Selection, Selection.End(xlDown)).Select
    range(Selection, Selection.End(xlToRight)).Select

    Set range1 = Selection

    range("L2").Select
    Set testvalue = Selection

    For Each A In range1
    For Each B In range1
    If B.Value <> A.Value Then
    If Abs(A - B) <= testvalue Then
    Sheets("Test Results").Select
    range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.Value = "Interference"
    ActiveCell.Offset(0, 1).Select
    Selection.Value = A.Value
    ActiveCell.Offset(0, 1).Select
    Selection.Value = B.Value
    End If
    End If
    Next
    Next

    End Sub



    Scenario 2 (code not working - getting "Runtime Error '13': Type mismatch"
    on the line 'If Abs(A - B) <= testvalue Then' below):

    Sub test1()

    Dim A
    Dim B
    Dim C
    Dim testvalue
    Dim range1 As range

    Sheets("Frequency Input").Select
    range("A6").Select
    range(Selection, Selection.End(xlDown)).Select
    range(Selection, Selection.End(xlToRight)).Select

    Set range1 = Selection

    range("L2").Select
    Set testvalue = Selection

    For Each A In range1
    For Each B In range1
    If B.Value <> A.Value Then
    If Abs(A - B) <= testvalue Then
    Sheets("Test Results").Select
    range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.Value = "Interference"
    ActiveCell.Offset(0, 1).Select
    Selection.Value = A.Value
    ActiveCell.Offset(0, 1).Select
    Selection.Value = B.Value
    End If
    End If
    Next
    Next

    End Sub


    Can anyone help me understand why in the world this is happening? It's
    driving me crazy....Thanks in advance!

  2. #2
    Gary Brown
    Guest

    RE: Runtime Error '13': Type mismatch

    1) What line's it bombing out on?
    2) How far down the selection is it getting before bombing out?
    3) Have you looked at that cell to see if there is a data type problem?

    The only difference in the code that I can see is that you start at
    different places. A1 vs. A6
    This suggests to me that you are getting a different selection of data in
    the 'good' code vs. the bad code. Take a look at what those two selection
    areas are.

    Put ...
    Debug.Print Selection.Address
    in your code just before 'Set range1 = Selection', run the code and look at
    the immediate window to see what the range is in each program.

    To check the 'current cell' where the code bombs out, put...
    Debug.Pring A.Address
    in your code just after 'For Each A in range1'

    HTH,
    --
    Gary Brown
    [email protected]


    "Linking to specific cells in pivot table" wrote:

    > Hi - as I am developing a spreadsheet that I am working on, I am saving new
    > versions with every somewhat significant change I make to the spreadsheet.
    > For some reason, the ALMOST identical code works in one version of the
    > spreadsheet, but not in the other. In the scenario it is not working, I am
    > getting the error "Runtime Error '13': Type mismatch". Below is the code
    > for both scenarios:
    >
    > - Scenario 1 (code works fine)
    >
    > Sub test1()
    >
    > Dim A
    > Dim B
    > Dim C
    > Dim testvalue
    > Dim range1 As range
    >
    > Sheets("Frequency Input").Select
    > range("A1").Select
    > range(Selection, Selection.End(xlDown)).Select
    > range(Selection, Selection.End(xlToRight)).Select
    >
    > Set range1 = Selection
    >
    > range("L2").Select
    > Set testvalue = Selection
    >
    > For Each A In range1
    > For Each B In range1
    > If B.Value <> A.Value Then
    > If Abs(A - B) <= testvalue Then
    > Sheets("Test Results").Select
    > range("A1").Select
    > Selection.End(xlDown).Select
    > ActiveCell.Offset(1, 0).Select
    > Selection.Value = "Interference"
    > ActiveCell.Offset(0, 1).Select
    > Selection.Value = A.Value
    > ActiveCell.Offset(0, 1).Select
    > Selection.Value = B.Value
    > End If
    > End If
    > Next
    > Next
    >
    > End Sub
    >
    >
    >
    > Scenario 2 (code not working - getting "Runtime Error '13': Type mismatch"
    > on the line 'If Abs(A - B) <= testvalue Then' below):
    >
    > Sub test1()
    >
    > Dim A
    > Dim B
    > Dim C
    > Dim testvalue
    > Dim range1 As range
    >
    > Sheets("Frequency Input").Select
    > range("A6").Select
    > range(Selection, Selection.End(xlDown)).Select
    > range(Selection, Selection.End(xlToRight)).Select
    >
    > Set range1 = Selection
    >
    > range("L2").Select
    > Set testvalue = Selection
    >
    > For Each A In range1
    > For Each B In range1
    > If B.Value <> A.Value Then
    > If Abs(A - B) <= testvalue Then
    > Sheets("Test Results").Select
    > range("A1").Select
    > Selection.End(xlDown).Select
    > ActiveCell.Offset(1, 0).Select
    > Selection.Value = "Interference"
    > ActiveCell.Offset(0, 1).Select
    > Selection.Value = A.Value
    > ActiveCell.Offset(0, 1).Select
    > Selection.Value = B.Value
    > End If
    > End If
    > Next
    > Next
    >
    > End Sub
    >
    >
    > Can anyone help me understand why in the world this is happening? It's
    > driving me crazy....Thanks in advance!


  3. #3
    Linking to specific cells in pivot table
    Guest

    RE: Runtime Error '13': Type mismatch

    Hi Gary - I entered in the debug code you instructed -- when I ran the macro
    afterwards however, nothing was different. Is there something else I'm
    supposed to do in order to see the results of the debug code?

    Thanks,

    Rob

    "Gary Brown" wrote:

    > 1) What line's it bombing out on?
    > 2) How far down the selection is it getting before bombing out?
    > 3) Have you looked at that cell to see if there is a data type problem?
    >
    > The only difference in the code that I can see is that you start at
    > different places. A1 vs. A6
    > This suggests to me that you are getting a different selection of data in
    > the 'good' code vs. the bad code. Take a look at what those two selection
    > areas are.
    >
    > Put ...
    > Debug.Print Selection.Address
    > in your code just before 'Set range1 = Selection', run the code and look at
    > the immediate window to see what the range is in each program.
    >
    > To check the 'current cell' where the code bombs out, put...
    > Debug.Pring A.Address
    > in your code just after 'For Each A in range1'
    >
    > HTH,
    > --
    > Gary Brown
    > [email protected]
    >
    >
    > "Linking to specific cells in pivot table" wrote:
    >
    > > Hi - as I am developing a spreadsheet that I am working on, I am saving new
    > > versions with every somewhat significant change I make to the spreadsheet.
    > > For some reason, the ALMOST identical code works in one version of the
    > > spreadsheet, but not in the other. In the scenario it is not working, I am
    > > getting the error "Runtime Error '13': Type mismatch". Below is the code
    > > for both scenarios:
    > >
    > > - Scenario 1 (code works fine)
    > >
    > > Sub test1()
    > >
    > > Dim A
    > > Dim B
    > > Dim C
    > > Dim testvalue
    > > Dim range1 As range
    > >
    > > Sheets("Frequency Input").Select
    > > range("A1").Select
    > > range(Selection, Selection.End(xlDown)).Select
    > > range(Selection, Selection.End(xlToRight)).Select
    > >
    > > Set range1 = Selection
    > >
    > > range("L2").Select
    > > Set testvalue = Selection
    > >
    > > For Each A In range1
    > > For Each B In range1
    > > If B.Value <> A.Value Then
    > > If Abs(A - B) <= testvalue Then
    > > Sheets("Test Results").Select
    > > range("A1").Select
    > > Selection.End(xlDown).Select
    > > ActiveCell.Offset(1, 0).Select
    > > Selection.Value = "Interference"
    > > ActiveCell.Offset(0, 1).Select
    > > Selection.Value = A.Value
    > > ActiveCell.Offset(0, 1).Select
    > > Selection.Value = B.Value
    > > End If
    > > End If
    > > Next
    > > Next
    > >
    > > End Sub
    > >
    > >
    > >
    > > Scenario 2 (code not working - getting "Runtime Error '13': Type mismatch"
    > > on the line 'If Abs(A - B) <= testvalue Then' below):
    > >
    > > Sub test1()
    > >
    > > Dim A
    > > Dim B
    > > Dim C
    > > Dim testvalue
    > > Dim range1 As range
    > >
    > > Sheets("Frequency Input").Select
    > > range("A6").Select
    > > range(Selection, Selection.End(xlDown)).Select
    > > range(Selection, Selection.End(xlToRight)).Select
    > >
    > > Set range1 = Selection
    > >
    > > range("L2").Select
    > > Set testvalue = Selection
    > >
    > > For Each A In range1
    > > For Each B In range1
    > > If B.Value <> A.Value Then
    > > If Abs(A - B) <= testvalue Then
    > > Sheets("Test Results").Select
    > > range("A1").Select
    > > Selection.End(xlDown).Select
    > > ActiveCell.Offset(1, 0).Select
    > > Selection.Value = "Interference"
    > > ActiveCell.Offset(0, 1).Select
    > > Selection.Value = A.Value
    > > ActiveCell.Offset(0, 1).Select
    > > Selection.Value = B.Value
    > > End If
    > > End If
    > > Next
    > > Next
    > >
    > > End Sub
    > >
    > >
    > > Can anyone help me understand why in the world this is happening? It's
    > > driving me crazy....Thanks in advance!


+ 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