expected end of statement require error

[akkinarula]

hi all ,
i m getting following error while concatinating following string which i m taking from mstr1 and mstr2 .a is the variable which takes value from loop.

mstr1 = "'Customer''s Details'!R" & Trim(Str(a)) & "C39"
mstr2 = "'Customer''s Details'!R" & Trim(Str(a)) & "C43"
str1 = "=(CONCATENATE (" & mstr1 " & mstr2")"


plz tell me where i m missing something.

 

[GTO]

Maybe:
mstr1 = "'Customer''s Details'!R" & Trim(Str(a)) & "C39"
mstr2 = "'Customer''s Details'!R" & Trim(Str(a)) & "C43"
str1 = "=(CONCATENATE (" & mstr1 & mstr2 & ")"

But I am curious. Do you really have two apostrophe's where indicated?

 

[energman58]

I dont think you need to use CONCATENATE here as it gives exactly the same results as "&"

What is the value you are trying to get for str1? If it is MSTR1 and MSTR2 separated by a space this will do it:

MSTR1&" "&MSTR2

But I think what you are trying to do is to join the values from the references given so what I think you need is:

str1 = Range(mstr1).Value & " " & Range(mstr2).Value

(You can actually omit the Value bit as the property is assumed to be Value by default unless you define it as something else)

If you want to preserve the format of the two values (say one contains $12.30 (formatted as currency) and the other Overdrawn (text) you want it to display as $12.30 Overdrawn and not 12.3 Overdrawn) use Text instead of Value in the above line.

But I am not entirely convinced your references work - have you tried spitting out MSTR1 and MSTR2 using message boxes to check that the syntax is coming out correctly?

 

[akkinarula]

I dont think you need to use CONCATENATE here as it gives exactly the same results as "&"

What is the value you are trying to get for str1? If it is MSTR1 and MSTR2 separated by a space this will do it:

MSTR1&" "&MSTR2

But I think what you are trying to do is to join the values from the references given so what I think you need is:

str1 = Range(mstr1).Value & " " & Range(mstr2).Value

(You can actually omit the Value bit as the property is assumed to be Value by default unless you define it as something else)

If you want to preserve the format of the two values (say one contains $12.30 (formatted as currency) and the other Overdrawn (text) you want it to display as $12.30 Overdrawn and not 12.3 Overdrawn) use Text instead of Value in the above line.

But I am not entirely convinced your references work - have you tried spitting out MSTR1 and MSTR2 using message boxes to check that the syntax is coming out correctly?

it worked as i tried following way

mstr1 = "'Customer''s Details'!R" & Trim(Str(a)) & "C39"
mstr2 = "'Customer''s Details'!R" & Trim(Str(a)) & "C43"
str1 = "=CONCATENATE(" & mstr1 & ","" "" " & "," & mstr2 & ")"