countif using concate function

[akkinarula]

hi all ,
i have tried following formula it runs without error but it does not show result

crange2 = "G" & Trim(Str(i)) & ":H" & Trim(Str(i))

Sheets("Rcl Mix").Select

Range(crange2).Select

ActiveCell.FormulaR1C1 = "=COUNTIF('Customer''s Details'!R13C53:R262C53,(CONCATENATE('RCL Mix'!R" & Trim(Str(i)) & "C2,R9C7)))"

i think underlined sentence is not working is anything i have written wrong
plz help me

 

[villy]

(CONCATENATE('RCL Mix'!R" & Trim(Str(i)) & "C2,R9C7)))"

I think you cannot use CONCATENATE and & at the same time as it is the same..
Either use CONCATENATE(text1,text2,...) or text1 & text2 & ...

 

[akkinarula]

I think you cannot use CONCATENATE and & at the same time as it is the same..
Either use CONCATENATE(text1,text2,...) or text1 & text2 & ...

i have tried this it is worked

pstr1 = "=CONCATENATE('Customer''s Details'!R" & Trim(Str(a)) & "C11" & ","" "")"


ActiveCell.FormulaR1C1 = pstr1

 

[akkinarula]

i have tried this it is worked

pstr1 = "=CONCATENATE('Customer''s Details'!R" & Trim(Str(a)) & "C11" & ","" "")"


ActiveCell.FormulaR1C1 = pstr1

ok now i have removed & part and written as

crange1 = "G" & Trim(Str(i)) & ":H" & Trim(Str(i))
cstr1 = "B" & Trim(Str(i)) & ":C" & Trim(Str(i))
cstr2 = "G9:H9"
Range(crange1).Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('Customer''s Details'!R13C53:R262C53,(Concatenate(cstr1,cstr2)))"


still result is not displaying .
plz anybody knows the solution for this plz tell me.