ExcelBanter - gather data in selected cells and transfer value to another cell

I'm having trouble reproducing your formula result. Is each of the 50
columns handled the same or is each specified differently? When I put
Chinny in F37 where your result requires it, the ROUND gives #VALUE!.
AP37 can have text, but ROUND(AP38,0) gives 46, not 45.77. Also, the
formula results in @ between values, not "@"

Assuming you want labels in M1, AT2, column C of the first row of each
pair, and columns F:AP, then try
Sub myCatBuilder()
Const a$ = ",""@"","
' Const a$ = ",""""""@"""""","
Dim s As String, col As Integer
s = "=CONCATENATE($M$1,""@"",$AT$2"
For col = Cells(1, "F").Column To Cells(1, "AP").Column
s$ = s$ & a$ & Cells(37, col).Address(False, False) _
& a$ & "ROUND(" & Cells(38, col).Address(False, False) & ",
2)"
Next col
s$ = s$ & ")"
Range("A1").Formula = s$
End Sub
If you don't have contiguous columns, then you will need more for
loops. This gives ROUND(AP38,2).
If you really want "@" to appear in your result, use the second a$
const.

Carl.

On May 7, 12:14 am, chinny wrote:
Afternoon,

I want to create a script (or macro) that will run a program that looks at
speific cells, get their values (regards if value is zero) and display the
results in A1. Current i'm using the below line but it is tedious and tiring
considering i have over 50 columns. Please advise when you can. Cheers.

Below line, the result is displayed in A1 as "
etc...
=CONCATENATE($M$1,"@",$AT$2,"@",C37,"@",ROUND(F37, 2),"@",ROUND(F38,2),"@",R*OUND(G37,2),"@",ROUND(G3 8,2),"@",ROUND(H37,2),"@",ROUND(H38,2),"@",ROUND(J 3*7,2),"@",ROUND(J38,2),"@",AP37,"@",ROUND(AP38,0) )