Getting type mismatch error without any modification in VBA code

Question

Hi I have a code and I didn't make any change, but I am getting Type mismatch error. It was working properly previously.

Code :

Private Sub UserForm_Initialize()

    Dim r As Integer
    Dim c As Integer
    Me.ComboBox1.RowSource = "Intro!A3:A" & Range("A" & Rows.Count).End(xlUp).Row
    r = Application.Match("c", Columns(1), 0)
    c = Application.Match("cc", Rows(1), 0)
    TextBox1.Value = Cells(r, c).Value
    TextBox2.Value = Cells(r, c + 1).Value
    TextBox3.Value = Cells(r, c + 2).Value
    TextBox4.Value = Cells(r, c + 3).Value


End Sub

Ran using f8 through code found error when reaching here :

 c = Application.Match("cc", Rows(1), 0)

Objective of this line to find match in first row

or change it to String if you want to. Currently you assign it as Integer but you're look for String type — aaa
– aaa, Commented Mar 20, 2018 at 6:23
Is it possible that there used to be a "cc" in row 1 but now there is not? — Tony M
– Tony M, Commented Mar 20, 2018 at 6:23

Shai Rado · Accepted Answer · 2018-03-20 06:59:01Z

2

When using Match you should always prepare your code for the scenario where Match failed to find the value (or String) you are looking for.

You can do that by using:

If Not IsError(Application.Match("cc", Columns(1), 0)) Then

Modified Code

Dim r As Variant
Dim c As Variant

Me.ComboBox1.RowSource = "Intro!A3:A" & Range("A" & Rows.Count).End(xlUp).Row

If Not IsError(Application.Match("c", Columns(1), 0)) Then
    r = Application.Match("c", Columns(1), 0)
Else
    ' in case "c" is not found
    MsgBox "critical error finding 'c'"
End If

If Not IsError(Application.Match("cc", Columns(1), 0)) Then
    c = Application.Match("cc", Columns(1), 0)
Else
    ' in case "cc" is not found
    MsgBox "critical error finding 'cc'"
End If

answered Mar 20, 2018 at 6:59

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fratyx · Accepted Answer · 2018-03-20 06:17:54Z

1

I suppose there is no 'cc' in first row. In this case Application.Match returns an Error object which can't be assigned to an integer.

answered Mar 20, 2018 at 6:17

Fratyx

5,8271 gold badge16 silver badges24 bronze badges

1 Comment

Fratyx Over a year ago

Are you sure that there is only this two character string 'cc' in a cell? Maybe try as suggested in first comment: Dim c As Variant and watch the result, if there is an error.

Collectives™ on Stack Overflow

Getting type mismatch error without any modification in VBA code

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related