VBA type mismatch error

  • Thread starter Thread starter Garbunkel
  • Start date Start date
G

Garbunkel

Hello,

What I am aiming to do is create VBA code that will
interpret data, parsed from a cell that the user inputs
to & change the color of the text if the user has entered
a duplicate entry.

After hacking at this in VBA for a while, I did get it to work
(almost!). The only problem is when there is a duplicate entry, the
text formatting works, but a Type Mismatch error comes up. Here is the
code I wrote:


Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub


If Not Application.Intersect(Range("A­1:A3000"), Target) Is
Nothing
Then


Sheets("A").Activate
Dim i As Integer
Dim RowCount As Integer
Dim CellVaLue


Application.ScreenUpdating = False

Range("J65535").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
RowCount = Selection.Rows.Count

'define a starting place
Range("J1").Select

Target.Resize(1, 1).Font.ColorIndex = 1
Target.Resize(1, 1).Font.strikethrough = False


'Begining of Loop
'Loop through each of the rows of the spreadsheet
For i = 1 To RowCount


'IF I REMOVE THE '10' FROM THIS LINE, THE ERROR
'GOES AWAY, BUT THEN THE TEXT FORMATTING DOESN'T WORK
CellVaLue = Cells(i + 1, 10).Value


'THIS IS THE LINE THE DEBUGGER HIGHLIGHTS
If (CellVaLue = (Target.Offset(0, 9).Value)) Then

Target.Resize(1, 1).Font.ColorIndex = 7
Target.Resize(1, 1).Font.strikethrough = True
End If


Next


If ((Target.Offset(0, 5).Value = "1") And (Target.Offset(0,
6).Value <> "Yes")) Then
Target.Resize(1, 1).Font.ColorIndex = 3
Target.Resize(1, 1).Font.strikethrough = False
End If
End If
End Sub


Any help is greatly appreciated.

Thanks,
Michael
 
What type of data is contained on each side of this comparison?
If (CellVaLue = (Target.Offset(0, 9).Value)) Then

If you are comparing something (number or text) to a worksheet error (#N/A)
then you would get a type mismatch error. You could also get this error
comparing a number to a string.

You could first test for an error with

Application.IsError(CellValue)
Application.IsError(Target.Offset(0, 9).Value))

or you could try converting each item to a string with
If (UCase(Cstr(CellVaLue)) = UCase(CStr(Target.Offset(0, 9).Value))) Then

Double check your data types. Either test the data types before your
comparison or force both items to the same data type.
 
This code confuses me somewhat.

I put 1,2,3,4,5,1, in J1:J6 on a sheet, and then entered a in A1, and a1
became highlighted? Is this what should happen?

The other thing I don't get is the sheets acted upon. Is this code behind
the 'A' sheet or some other sheet?

--
HTH

Bob Phillips

Hello,

What I am aiming to do is create VBA code that will
interpret data, parsed from a cell that the user inputs
to & change the color of the text if the user has entered
a duplicate entry.

After hacking at this in VBA for a while, I did get it to work
(almost!). The only problem is when there is a duplicate entry, the
text formatting works, but a Type Mismatch error comes up. Here is the
code I wrote:


Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub


If Not Application.Intersect(Range("A­1:A3000"), Target) Is
Nothing
Then


Sheets("A").Activate
Dim i As Integer
Dim RowCount As Integer
Dim CellVaLue


Application.ScreenUpdating = False

Range("J65535").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
RowCount = Selection.Rows.Count

'define a starting place
Range("J1").Select

Target.Resize(1, 1).Font.ColorIndex = 1
Target.Resize(1, 1).Font.strikethrough = False


'Begining of Loop
'Loop through each of the rows of the spreadsheet
For i = 1 To RowCount


'IF I REMOVE THE '10' FROM THIS LINE, THE ERROR
'GOES AWAY, BUT THEN THE TEXT FORMATTING DOESN'T WORK
CellVaLue = Cells(i + 1, 10).Value


'THIS IS THE LINE THE DEBUGGER HIGHLIGHTS
If (CellVaLue = (Target.Offset(0, 9).Value)) Then

Target.Resize(1, 1).Font.ColorIndex = 7
Target.Resize(1, 1).Font.strikethrough = True
End If


Next


If ((Target.Offset(0, 5).Value = "1") And (Target.Offset(0,
6).Value <> "Yes")) Then
Target.Resize(1, 1).Font.ColorIndex = 3
Target.Resize(1, 1).Font.strikethrough = False
End If
End If
End Sub


Any help is greatly appreciated.

Thanks,
Michael
 
Okay, I got it, it was indeed a string compared to an integer & the
UCase took care of it. Thanks a lot!
 
Back
Top