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!

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
gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com


"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!

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
> gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com
>
>
> "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!