Ms Excel VBA type mismatch error

Question

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

Answer 1

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