Search code examples
excelvbaexceptiontypesmismatch

Ms Excel VBA type mismatch error

I am receiving type mismatch error when I use below mentioned code.If possible please help to resolve below mentioned code error.Both AP# and AR3 have charactor values.

What my basic requirment is use function just like concatenate function to add two charactor values from two cell in one excel cell using vba code

Range("AI3:AI" & lrv + 2).Value = Range("AP3:AP" & lrv + 2).Value & " " & Range("AR3:AR" & lrv + 2).Value
Solution

  • You are getting a mismatch error because this is an incorrect way of assigning values. For example, this is incorrect

    Range("D1:D3").Value = Range("A1:A3").Value & " " & Range("B1:B3").Value

    You are working with array. So try this

    Range("D1:D3").FormulaArray = _
"=(" & Range("A1:A3").Address & ") & "" "" & (" & Range("B1:B3").Address & ")"

    So your code becomes

    Range("AI3:AI" & lrv + 2).FormulaArray = _
"=(" & _
Range("AP3:AP" & lrv + 2).Address & _
") & "" "" & (" & _
Range("AR3:AR" & lrv + 2).Address & ")"

    Or in a more simplified manner

    Dim rng1 As Range, rng2 As Range, rng3 As Range

Set rng1 = Range("AI3:AI" & lrv + 2)
Set rng2 = Range("AP3:AP" & lrv + 2)
Set rng3 = Range("AR3:AR" & lrv + 2)

rng1.FormulaArray = "=(" & _
                    rng2.Address & _
                    ") "" "" & (" & _
                    rng3.Address & _
                    ")"

rng1.Value = rng1.Value

rng2.Resize(1,2).ClearContents