Help with Concatenate formula in VBA

[tcnt9176]

I have a long concatenate formula that works in excel. However, I am having trouble when translating the formula into VBA. Here is the formula and the vba translation. I get a Syntax error in the VB editor.

=CONCATENATE(""""&A3&""""&","&""""&B3&""""&","&""""&C3&""""&","&""""&D3&""""&","&""""&E3&""""&","&""""&F3&""""&","&""""&G3&""""&","&""""&H3&""""&","&""""&I3&""""&","&""""&J3&""""&","&""""&K3&""""&","&""""&L3&""""&","&""""&M3&""""&","&""""&N3&""""&","&""""&O3&""""&","&""""&P3&""""&","&""""&Q3&""""&","&""""&R3&""""&","&""""&S3&""""&","&""""&T3&""""&","&""""&U3&""""&","&""""&V3&""""&","&""""&W3&""""&","&""""&X3&""""&","&""""&Y3&""""&","&""""&Z3&""""&","&""""&AA3&""""&","&""""&AB3&""""&","&""""&AC3&""""&","&""""&AD3&""""&","&""""&AE3&""""&","&""""&AF3&""""&","&""""&AG3&""""&","&""""&AH3&""""&","&""""&AI3&""""&","&""""&AJ3&""""&","&""""&AK3&""""&","&""""&AL3&""""&","&""""&AM3&""""&","&""""&AN3&""""&","&""""&AO3&""""&","&""""&AP3&""""&","&""""&AQ3&""""&","&""""&AR3&""""&","&""""&AS3&""""&","&""""&AT3&""""&","&""""&AU3&""""&","&""""&AV3&""""&","&""""&AW3&""""&","&""""&AX3&""""&","&""""&AY3&""""&","&""""&AZ3&""""&","&""""&BA3&""""&","&""""&BB3&""""&","&""""&BC3&""""&","&""""&BD3&""""&","&""""&BE3&""""&","&""""&BF3&""""&","&""""&BG3&""""&","&""""&BH3&""""&","&""""&BI3&""""&","&""""&BJ3&""""&","&""""&BK3&""""&","&""""&BL3&""""&","&""""&BM3&""""&","&""""&BN3&""""&","&""""&BO3&""""&","&""""&BP3&""""&","&""""&BQ3&""""&","&""""&BR3&""""&","&""""&BS3&""""&","&""""&BT3&""""&","&""""&BU3&""""&","&""""&BV3&""""&","&""""&BW3&""""&","&""""&BX3&""""&","&""""&BY3&""""&","&""""&BZ3&""""&","&""""&CA3&""""&","&""""&CB3&""""&","&""""&CC3&""""&","&""""&CD3&""""&","&""""&CE3&""""&","&""""&CF3&""""&","&""""&CG3&""""&","&""""&CH3&""""&","&""""&CI3&""""&","&""""&CJ3&""""&","&""""&CK3&""""&","&""""&CL3&""""&","&""""&CM3&""""&","&""""&CN3&""""&","&""""&CO3&""""&","&""""&CP3&""""&","&""""&CQ3&""""&","&""""&CR3&""""&","&""""&CS3&""""&","&""""&CT3&""""&","&""""&CU3&""""&","&""""&CV3&""""&","&""""&CW3&""""&","&""""&CX3&""""&","&""""&CY3&""""&","&""""&CZ3&""""&","&""""&DA3&""""&","&""""&DB3&""""&","&""""&DC3&""""&","&""""&DD3&""""&","&""""&DE3&""""&","&""""&DF3&""""&","&""""&DG3&""""&","&""""&DH3&""""&","&""""&DI3&""""&","&""""&DJ3&""""&","&""""&DK3&""""&","&""""&DL3&""""&","&""""&DM3&""""&","&""""&DN3&""""&","&""""&DO3&""""&","&""""&DP3&""""&","&""""&DQ3&""""&","&""""&DR3&""""&","&""""&DS3&""""&","&""""&DT3&""""&","&""""&DU3&""""&","&""""&DV3&""""&","&""""&DW3&""""&","&""""&DX3&""""&","&""""&DY3&""""&","&""""&DZ3&""""&EA3&""""&","&""""&EB3&""""&","&""""&EC3&""""&","&""""&ED3&""""&","&""""&EE3&""""&","&""""&EF3&""""&","&""""&EG3&""""&","&""""&EH3&""""&","&""""&EI3&""""&","&""""&EJ3&""""&","&""""&EK3&""""&","&""""&EL3&""""&","&""""&EM3&""""&","&""""&EN3&""""&","&""""&EO3&""""&","&""""&EP3&""""&","&""""&EQ3&""""&","&""""&ER3&""""&","&""""&ES3&""""&","&""""&ET3&""""&","&""""&EU3&""""&","&""""&EV3&""""&","&""""&EW3&""""&","&""""&EX3&""""&","&""""&EY3&""""&","&"""""")

ActiveCell.FormulaR1C1 = "=CONCATENATE _
(""""""""&RC[-155]&""""""""&"",""&""""""""&RC[-154]&""""""""&"",""&""""""""&RC[-153]&""""""""&"",""&""""""""&RC[-152]&""""""""&"",""&""""""""&RC[-151]&""""""""&"",""&""""""""&RC[-150]&""""""""&"",""&""""""""&RC[-149]&""""""""&"",""&""""""""&RC[-148]&""""""""&"",""&""""""""&RC[-147]&""""""""&"",""&""""""""&RC[-146]&""""""""&"",""&""""""""&RC[-145]&""""""""&"",""&""""""""&RC[-144]&""""""""&"",""&""""""""&RC[-143]&""""""""&"",""&""""""""&RC[-142]&""""""""&"",""&""""""""&RC[-141]&""""""""&"",""&""""""""&RC[-140]&""""""""&"",""&""""""""&RC[-139]&""""""""&"",""&""""""""&RC[-138]&""""""""&"",""&""""""""&RC[-137]&""""""""&"",""&""""""""&RC[-136]&""""""""&"",""&""""""""&RC[-135]&""""""""&"",""&""""""""&RC[-134]&""""""""&"",""&""""""""&RC[-133]&""""""""&"",""&""""""""&RC[-132]&""""""""&"",""&""""""""&RC[-131]&""""""""&"",""&""""""""&RC[-130]&""""""""&"",""&""""""""&RC[-129]&""""""""&"",""&""""""""&RC[-128]&""""""""&"",""&""""""""&RC[-127]&""""""""&"",""&""""""""&RC[-126]&""""""""&"",""&""""""""&RC _
[-125]&""""""""&"",""&""""""""&RC[-124]&""""""""&"",""&""""""""&RC[-123]&""""""""&"",""&""""""""&RC[-122]&""""""""&"",""&""""""""&RC[-121]&""""""""&"",""&""""""""&RC[-120]&""""""""&"",""&""""""""&RC[-119]&""""""""&"",""&""""""""&RC[-118]&""""""""&"",""&""""""""&RC[-117]&""""""""&"",""&""""""""&RC[-116]&""""""""&"",""&""""""""&RC[-115]&""""""""&"",""&""""""""&RC[-114]&""""""""&"",""&""""""""&RC[-113]&""""""""&"",""&""""""""&RC[-112]&""""""""&"",""&""""""""&RC[-111]&""""""""&"",""&""""""""&RC[-110]&""""""""&"",""&""""""""&RC[-109]&""""""""&"",""&""""""""&RC[-108]&""""""""&"",""&""""""""&RC[-107]&""""""""&"",""&""""""""&RC[-106]&""""""""&"",""&""""""""&RC[-105]&""""""""&"",""&""""""""&RC[-104]&""""""""&"",""&""""""""&RC[-103]&""""""""&"",""&""""""""&RC[-102]&""""""""&"",""&""""""""&RC[-101]&""""""""&"",""&""""""""&RC[-100]&""""""""&"",""&""""""""&RC[-99]&""""""""&"",""&""""""""&RC[-98]&""""""""&"",""&""""""""&RC[-97]&""""""""&"",""&""""""""&RC[-96]&""""""""& _
"",""&""""""""&RC[-95]&""""""""&"",""&""""""""&RC[-94]&""""""""&"",""&""""""""&RC[-93]&""""""""&"",""&""""""""&RC[-92]&""""""""&"",""&""""""""&RC[-91]&""""""""&"",""&""""""""&RC[-90]&""""""""&"",""&""""""""&RC[-89]&""""""""&"",""&""""""""&RC[-88]&""""""""&"",""&""""""""&RC[-87]&""""""""&"",""&""""""""&RC[-86]&""""""""&"",""&""""""""&RC[-85]&""""""""&"",""&""""""""&RC[-84]&""""""""&"",""&""""""""&RC[-83]&""""""""&"",""&""""""""&RC[-82]&""""""""&"",""&""""""""&RC[-81]&""""""""&"",""&""""""""&RC[-80]&""""""""&"",""&""""""""&RC[-79]&""""""""&"",""&""""""""&RC[-78]&""""""""&"",""&""""""""&RC[-77]&""""""""&"",""&""""""""&RC[-76]&""""""""&"",""&""""""""&RC[-75]&""""""""&"",""&""""""""&RC[-74]&""""""""&"",""&""""""""&RC[-73]&""""""""&"",""&""""""""&RC[-72]&""""""""&"",""&""""""""&RC[-71]&""""""""&"",""&""""""""&RC[-70]&""""""""&"",""&""""""""&RC[-69]&""""""""&"",""&""""""""&RC[-68]&""""""""&"",""&""""""""&RC[-67]&""""""""&"",""&""""""""&RC[-66]&""""""""&"",""&""""""""&RC[-65]&""""""""&"",""&""""""""&RC[-64]& _
""""""""&"",""&""""""""&RC[-63]&""""""""&"",""&""""""""&RC[-62]&""""""""&"",""&""""""""&RC[-61]&""""""""&"",""&""""""""&RC[-60]&""""""""&"",""&""""""""&RC[-59]&""""""""&"",""&""""""""&RC[-58]&""""""""&"",""&""""""""&RC[-57]&""""""""&"",""&""""""""&RC[-56]&""""""""&"",""&""""""""&RC[-55]&""""""""&"",""&""""""""&RC[-54]&""""""""&"",""&""""""""&RC[-53]&""""""""&"",""&""""""""&RC[-52]&""""""""&"",""&""""""""&RC[-51]&""""""""&"",""&""""""""&RC[-50]&""""""""&"",""&""""""""&RC[-49]&""""""""&"",""&""""""""&RC[-48]&""""""""&"",""&""""""""&RC[-47]&""""""""&"",""&""""""""&RC[-46]&""""""""&"",""&""""""""&RC[-45]&""""""""&"",""&""""""""&RC[-44]&""""""""&"",""&""""""""&RC[-43]&""""""""&"",""&""""""""&RC[-42]&""""""""&"",""&""""""""&RC[-41]&""""""""&"",""&""""""""&RC[-40]&""""""""&"",""&""""""""&RC[-39]&""""""""&"",""&""""""""&RC[-38]&""""""""&"",""&""""""""&RC[-37]&""""""""&"",""&""""""""&RC[-36]&""""""""&"",""&""""""""&RC[-35]&""""""""&"",""&""""""""&RC[-34]&""""""""&"",""&""""""""&RC[-33]&""""""""&"",""&""""""""& _
RC[-32]&""""""""&"",""&""""""""&RC[-31]&""""""""&"",""&""""""""&RC[-30]&""""""""&"",""&""""""""&RC[-29]&""""""""&"",""&""""""""&RC[-28]&""""""""&"",""&""""""""&RC[-27]&""""""""&"",""&""""""""&RC[-26]&""""""""&RC[-25]&""""""""&"",""&""""""""&RC[-24]&""""""""&"",""&""""""""&RC[-23]&""""""""&"",""&""""""""&RC[-22]&""""""""&"",""&""""""""&RC[-21]&""""""""&"",""&""""""""&RC[-20]&""""""""&"",""&""""""""&RC[-19]&""""""""&"",""&""""""""&RC[-18]&""""""""&"",""&""""""""&RC[-17]&""""""""&"",""&""""""""&RC[-16]&""""""""&"",""&""""""""&RC[-15]&""""""""&"",""&""""""""&RC[-14]&""""""""&"",""&""""""""&RC[-13]&""""""""&"",""&""""""""&RC[-12]&""""""""&"",""&""""""""&RC[-11]&""""""""&"",""&""""""""&RC[-10]&""""""""&"",""&""""""""&RC[-9]&""""""""&"",""&""""""""&RC[-8]&""""""""&"",""&""""""""&RC[-7]&""""""""&"",""&""""""""&RC[-6]&""""""""&"",""&""""""""&RC[-5]&""""""""&"",""&""""""""&RC[-4]&""""""""&"",""&""""""""&RC[-3]&""""""""&"",""&""""""""&RC[-2]&""""""""&"",""&""""""""&RC[-1]&""""""""&"",""&"""""""""""")"

 

[RSXchin]

Please post an example of what you want the formula to show.

 

[AlphaFrog]

I understand what the formula does. A couple of questions if you don't mind.

1.) Since you're using VBA, you could have the VBA code create the concatenated result without the need of a long worksheet formula. Then just paste the concatenated string in the cells. Would that be better?

2) Are you doing this to ultimately write the data to a text file? That too could be done directly in VBA code without the need to use a long worksheet formula.

Perhaps if you gave some details about your project, there may be a better method in VBA than using a long worksheet formula.

 

[Jonmo1]

Agreeing with AlphaFrogg (wholeheartedly).
There's got to be a better way...


But, to answer the question...

When building formulas in VBA, and you want the resulting formula to contain a quote, you have to double up those quotes...

So if your formula contains a string of 4 quotes, then in vba you must put 8 quotes.

This
=CONCATENATE(""""&A3&""""&","&""""
becomes
=CONCATENATE(""""""""&A3&""""""""&"",""&""""""""


Incidentally, you don't need to use Concatenate, you're already using the &.
They both do the same thing...

It's like writing
=SUM(A1+B1)
Why bother with the SUM, you've already summed by doing A1+B1

so

=CONCATENATE(""""""""&A3&""""""""&"",""&"""""""")
becomes
=""""""""&A3&""""""""&"",""&""""""""


Hope that helps.