0

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.

2
  • Change + to &. Commented Aug 17, 2021 at 19:21
  • 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. Commented Aug 17, 2021 at 19:33

1 Answer 1

0

Concatenation operator in VBA is &, not +.

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.