VBA concantenate formula creation excel 2010

[kainfriends]

Hello!
I am creating an excel spreadsheet that will update numbers based product id and product type.
i identify the column by concantenating both into a new cell which is my index.

currently i have VBA primed to add more columns but i have trouble with this one little piece.

testval = ("CONCANTENATE(" & add1 & ","" """ & "," & add2& ") ")
Where add1 = M2, and add2 = M4

the expected output
testval = "CONCANTENATE(M2," ",M4)

I currently get a compile error "expected"
i have tried removing the end part
testval = ("CONCANTENATE(" & add1 & ","" """ & "," & add2)
and it yields
"CONCANTENATE(M2," ",M4"

i am still testing and i don't know if the formula will work without the closing parentheses

thanks for your help in advance

 

[Scott Huish]

CONCATENATE is a worksheet function, not a VBA function, just use the ampersand &

testval=add1 & "," & add2

 

[kainfriends]

CONCATENATE is a worksheet function, not a VBA function, just use the ampersand &

testval=add1 & "," & add2

thanks for the reply.

i am trying to put the concantenate formula in a cell using VBA. I am concerned that if i use the code above, the result will only be recalculated when the code is run. I would prefer excel to recalculate this whenever any changes are made.

hope this gives better definition. if the above will recalculate just like the formula, let me know.

thanks again!

 

[Scott Huish]

Not sure why you're using a variable to hold the formula. You need to assign the formula to a range:

Sub test()
Dim add1 As String, add2 As String
add1 = "M2"
add2 = "M4"
Range("A1").Formula = "=CONCATENATE(" & add1 & "," & Chr(34) & " " & Chr(34) & "," & add2 & ")"
End Sub

 

[Scott Huish]

Alternatively, since there really isn't much point to the CONCATENATE function, this would also do what you want:

Sub test()
Dim add1 As String, add2 As String
add1 = "M2"
add2 = "M4"
Range("A1").Formula = "=" & add1 & "&" & Chr(34) & " " & Chr(34) & "&" & add2
End Sub