Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
gather data in selected cells and transfer value to another cell
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),"@",ROUND(G37,2),"@",ROUND(G38 ,2),"@",ROUND(H37,2),"@",ROUND(H38,2),"@",ROUND(J3 7,2),"@",ROUND(J38,2),"@",AP37,"@",ROUND(AP38,0)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
Macro to transfer contents of 'Selected' cell to alternate cell. | Excel Worksheet Functions | |||
auto transfer data in sheet2 (selected data) | Excel Worksheet Functions | |||
need to jump down 4 cells, gather data, jump another 4 | Excel Programming | |||
Automatically transfer selected data to another sheet on print | Excel Programming |