Runtime error '13': Type mismatch
Solved
SLM-BHY
Posted messages
29
Status
Membre
-
Heliotte Posted messages 1561 Status Membre -
Heliotte Posted messages 1561 Status Membre -
Hello,
I created a VBA macro that assigns a value (text) in column D if it finds a predefined value in column B.
For example, if cell B6 has "Car," then cell D6 will automatically have "Rented."
My macro works very well for each value entered. Now, if I have the same value to apply to a range of 10 rows, by extending the first value to the other 9, I get a debugging error with the Type Mismatch error.
Can you please help me?
Here’s a snippet of code:
Private Sub Worksheet_Change(ByVal Target As Range)
line = Target.Row
col = Target.Column
If Target.Column = 2 Then
If Target = "car" Then
Cells(line, 4).Value = "rented"
End If
<config>Windows XP / Internet Explorer 7.0</config>
I created a VBA macro that assigns a value (text) in column D if it finds a predefined value in column B.
For example, if cell B6 has "Car," then cell D6 will automatically have "Rented."
My macro works very well for each value entered. Now, if I have the same value to apply to a range of 10 rows, by extending the first value to the other 9, I get a debugging error with the Type Mismatch error.
Can you please help me?
Here’s a snippet of code:
Private Sub Worksheet_Change(ByVal Target As Range)
line = Target.Row
col = Target.Column
If Target.Column = 2 Then
If Target = "car" Then
Cells(line, 4).Value = "rented"
End If
<config>Windows XP / Internet Explorer 7.0</config>
3 réponses
Hello
There is indeed a problem with your code
When you select a range of multiple cells, your Target object contains multiple cells and your comparison If Target = "car" runs into a type issue.
You need to loop through all the cells in Target to compare only one cell at a time.
There is indeed a problem with your code
When you select a range of multiple cells, your Target object contains multiple cells and your comparison If Target = "car" runs into a type issue.
You need to loop through all the cells in Target to compare only one cell at a time.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer, col As Integer ' ' For row = Target.Row To Target.Row + Target.Rows.Count - 1 For col = Target.Column To Target.Column + Target.Columns.Count - 1 If col = 2 Then If Cells(row, col) = "car" Then Cells(row, 4).Value = "rented" End If End If Next col Next row End Sub
Best wishes!
I didn’t catch “on a 10-line beach” because I skimmed (too rushed this morning)
Thanks for the explanations .. it will serve as a lesson!