CODEWITHSUNDEEP
vbaexcelsearch
DG85
DG85

Reputation: 43

<Excel-VBA> "Type mismatch" error while copying value from cell into textbox

I am currently doing up a simple Userform <> Worksheet data editing interface, located in the same workbook. My userform buttons are on Sheet A and the Database (from which data will be pulled) is in another sheet. I am currently working on the search function (the entire block of code which is included below) and I am experiencing a "type mismatch" error at the following line:

MsgBox ws.Range("B" + cRow).Value

I have tried using the CVar() and other alternatives but it does not resolve the problem.

My intended workflow is that when a user types in a company name in the 'txtCompany' textbox and clicks the search button, it will search the database in the 'Company Name' (Column D) column for a similar name and return all other values in that row to my textboxes in the userform.

Would kindly appreciate if anyone could enlighten me as to what is causing this problem. The entire code for the Sub is given below:

Private Sub btnSearch_Click()

Dim lRow As Long
Dim ws As Worksheet
Dim srcterm As String
Dim datevalue As String
Dim cCol, cRow As Integer

Set ws = ThisWorkbook.Worksheets("Database")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Dim x As Control
For Each x In frmSearch.Controls
    If TypeName(x) = "TextBox" Then
    x.Value = ""
    End If
Next x

srcterm = txtCompany.Value
MsgBox lRow

For Each cell In ws.Range("D3:D" & lRow)

    If cell.Value Like srcterm Then

        cRow = cell.Row
        MsgBox cRow
        MsgBox ws.Range("B" + cRow).Value

        With frmSearch
            .txtDate.Value = ws.Range("B" + cRow).Value
            .txtCustomer.Value = ws.Cells("C" + cRow).Value
            .txtCompany.Value = ws.Cells("D" + cRow).Value
            .txtAddress.Value = ws.Cells("E" + cRow).Value
            .txtContact.Value = ws.Cells("F" + cRow).Value
            .txtEmail.Value = ws.Cells("G" + cRow).Value
            .txtStatus.Value = ws.Cells("H" + cRow).Value
        End With

        datevalue = ws.Cells("A" + cRow).Value
    End If

Next cell

End Sub

Upvotes: 2

Views: 668

Answers (1)

A.S.H
A.S.H

Reputation: 29332

"B" + cRow

This is not how you concatenate a number to a string in VBA. You should use:

"B" & cRow
'  ^^^

Ok the + operator works for concatenating strings, i.e. "a" + "b" but when trying it on a string and a number, it's a type mismatch. You could use "B" + CStr(cRow) but I advice you to drop completely the use of the + operator for string concatenation in VBA and stick to the & operator for this matter.

Upvotes: 1

Related Questions