How to merge numerical and alphabet VBA?

Question

I GET VBA ERROR 13, TYPE MISMATCH ON THIS LINE:

rngCell.Offset(, 2).Value = m5 + " " + m3 'ALPHA-NUMERICAL VALUE

OTHERWISE EVERYTHING IS WORKING FINE.

CODE MENTIONED BELOW

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, m1 As Variant, m2 As Variant
Check1:
    If Application.Intersect(Target, Range("C11")) Is Nothing Then GoTo Check2
Application.EnableEvents = False
    For Each rngCell1 In Range("C11")
        v1 = rngCell1.Value
        If Len(v1) > 0 Then

            'See if the value is in your lookup table
            m1 = Application.VLookup(v1, ThisWorkbook.Sheets("PARTY DETAILS").Range("D4:E10001"), 2, False)

            'If found a match then replace wiht the vlookup result
            If Not IsError(m1) Then rngCell1.Value = m1

        End If
        Range("C11").Value = UCase(Range("C11").Value)
        Next
       Application.EnableEvents = True
'On Error GoTo hell
'If Target.Address(False, False) = "C11" And Target.Validation.Type = 3 Then
'    Range("D19:D28").Value = ""
'End If
HI:
Check2:
'On Error GoTo HELL:
If Application.Intersect(Target, Range("C18:C37")) Is Nothing Then GoTo Check3:
Dim m3 As Variant
Dim m5 As String
Dim m4 As Variant

For Each rngCell In Intersect(Target, Range("C18:C37"))
    If Len(rngCell.Value) > 0 Then
        m1 = Application.VLookup(rngCell.Value, ThisWorkbook.Sheets("Item Names").Range("D4:E10001"), 2, False) 'TEXT VALUE
        m2 = Application.VLookup(rngCell.Value, ThisWorkbook.Sheets("Item Names").Range("D4:K10001"), 3, False) 'NUMERIC VALUE
        m3 = Application.VLookup(rngCell.Value, ThisWorkbook.Sheets("Item Names").Range("D4:E10001"), 4, False) 'TEXT VALUE
        m5 = "1"
        m4 = Application.VLookup(rngCell.Value, ThisWorkbook.Sheets("Item Names").Range("D4:K10001"), 5, False) 'NUMERIC VALUE
        If Not IsError(m1) Then
            Application.EnableEvents = False
            rngCell.Value = m1  'TEXT VALUE
            rngCell.Offset(, 1).Value = m2  'NUMERIC VALUE
            rngCell.Offset(, 2).Value = m5 + " " + m3 'ALPHA-NUMERICAL VALUE
            rngCell.Offset(, 3).Value = m4  'NUMERIC VALUE
            Application.EnableEvents = True
        End If
    End If
Next
If m1 = "" Then
            rngCell.Offset(, 2).Value = ""
End If

'HELL:
Check3:
Calculate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I CANT FIND OUT WHERE I AM DOIND WRONG.

ANY HELP WILL MAKE MY DAY.

Please note for the future that ALL CAPS is interpreted as SHOUTING. There is no need to shout here, so please refrain from ALL CAPS. — BigBen, Aug 17 '21 at 19:33

score 0 · Accepted Answer · answered Aug 17 '21 at 19:22

0

Concatenation operator in VBA is &, not +.

answered Aug 17 '21 at 19:22

Máté Juhász

2,197
1
19
40

How to merge numerical and alphabet VBA?

1 Answers1