Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult concatenate No 2
After the first concatenate i want to concatenate all the rows under th
checked boxes! Be more specific i have 20 cells in 800 rows A B C D .... AA <-- columns X X X ... <-- cells used as check box Z Z Z ... Z <-- cells used as headers 1 3 4 5 ... N <-- cells with values THIS =concatenate("A3";"B3";"D3") happens using the following cod written by Frank Kabel ------------------------------------ sub foo() dim rng as range dim cell as range dim ret_str set rng = range ("A3:AA3") for each cell in rng if cell.offset(-2,0).value = "X" then ret_str = ret_str & cell.value end if next range("A4").value = ret_str end sub ---------------------------------------------------- Now , I want this to happen for all the next 800 rows =concatenate("A4";"B4";"D4") =concatenate("A5";"B5";"D5") =concatenate("A6";"B5";"D6") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult concatenate No 2
Hi
try the following (will put the concatenated value in column AB for each row sub foo() dim rng as range dim row_index as long dim col_index as integer dim ret_str set rng = range ("A3:AA800") for row_index = rng.row to rng.rows.count + rng.row - 1 ret_str = "" for col_index = rng.column to rng.columns.count + rng.column - 1 if cells(1,col_index).value = "X" then ret_str = ret_str & cells(row_index,col_index).value end if next cells(row_index,"AB").value = ret_str next end sub -- Regards Frank Kabel Frankfurt, Germany After the first concatenate i want to concatenate all the rows under the checked boxes! Be more specific i have 20 cells in 800 rows A B C D .... AA <-- columns X X X ... <-- cells used as check box Z Z Z ... Z <-- cells used as headers 1 3 4 5 ... N <-- cells with values THIS =concatenate("A3";"B3";"D3") happens using the following code written by Frank Kabel ------------------------------------ sub foo() dim rng as range dim cell as range dim ret_str set rng = range ("A3:AA3") for each cell in rng if cell.offset(-2,0).value = "X" then ret_str = ret_str & cell.value end if next range("A4").value = ret_str end sub ---------------------------------------------------- Now , I want this to happen for all the next 800 rows =concatenate("A4";"B4";"D4") =concatenate("A5";"B5";"D5") =concatenate("A6";"B5";"D6") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult concatenate No 2
Frank Kabel wrote:
*Hi try the following (will put the concatenated value in column AB for each row sub foo() dim rng as range dim row_index as long dim col_index as integer dim ret_str set rng = range ("A3:AA800") for row_index = rng.row to rng.rows.count + rng.row - 1 ret_str = "" for col_index = rng.column to rng.columns.count + rng.column - 1 if cells(1,col_index).value = "X" then ret_str = ret_str & cells(row_index,col_index).value end if next cells(row_index,"AB").value = ret_str next end sub -- Regards Frank Kabel Frankfurt, Germany After the first concatenate i want to concatenate all the row under the checked boxes! Be more specific i have 20 cells in 800 rows A B C D .... AA <-- columns X X X ... <-- cells used as check box Z Z Z ... Z <-- cells used as headers 1 3 4 5 ... N <-- cells with values THIS =concatenate("A3";"B3";"D3") happens using the followin code written by Frank Kabel ------------------------------------ sub foo() dim rng as range dim cell as range dim ret_str set rng = range ("A3:AA3") for each cell in rng if cell.offset(-2,0).value = "X" then ret_str = ret_str & cell.value end if next range("A4").value = ret_str end sub ---------------------------------------------------- Now , I want this to happen for all the next 800 rows =concatenate("A4";"B4";"D4") =concatenate("A5";"B5";"D5") =concatenate("A6";"B5";"D6") * Frank thanks, But its extremely, extremely slow. I had in mind if u could create the function for the first row. If i had the function "=concatenate("A3";"B3";"D3"....etc) " create in the row AA1 for example, i could use a macro to fill all the wa down the others cells. Thanks one more ****************** Stathis Greece - Patra -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult concatenate No 2
Hi
a function wouldn't be much faster. But you may give the following a try - download the free add-in Morefunc.xll (http://longre.free.fr/english) and install it (it includes the function MCONCAT) - now insert the following ARRAY formula in AB3 (entered with CTRL+SHIFT+ENTER): =MCONCAT(IF($A$1:$AA$1="X",A3:AA3,"")) and copy this down for all your rows -- Regards Frank Kabel Frankfurt, Germany Frank Kabel wrote: *Hi try the following (will put the concatenated value in column AB for each row sub foo() dim rng as range dim row_index as long dim col_index as integer dim ret_str set rng = range ("A3:AA800") for row_index = rng.row to rng.rows.count + rng.row - 1 ret_str = "" for col_index = rng.column to rng.columns.count + rng.column - 1 if cells(1,col_index).value = "X" then ret_str = ret_str & cells(row_index,col_index).value end if next cells(row_index,"AB").value = ret_str next end sub -- Regards Frank Kabel Frankfurt, Germany After the first concatenate i want to concatenate all the rows under the checked boxes! Be more specific i have 20 cells in 800 rows A B C D .... AA <-- columns X X X ... <-- cells used as check box Z Z Z ... Z <-- cells used as headers 1 3 4 5 ... N <-- cells with values THIS =concatenate("A3";"B3";"D3") happens using the following code written by Frank Kabel ------------------------------------ sub foo() dim rng as range dim cell as range dim ret_str set rng = range ("A3:AA3") for each cell in rng if cell.offset(-2,0).value = "X" then ret_str = ret_str & cell.value end if next range("A4").value = ret_str end sub ---------------------------------------------------- Now , I want this to happen for all the next 800 rows =concatenate("A4";"B4";"D4") =concatenate("A5";"B5";"D5") =concatenate("A6";"B5";"D6") * Frank thanks, But its extremely, extremely slow. I had in mind if u could create the function for the first row. If i had the function "=concatenate("A3";"B3";"D3"....etc) " created in the row AA1 for example, i could use a macro to fill all the way down the others cells. Thanks one more ****************** Stathis Greece - Patras --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult concatenate No 2
Thats all i wanted to do!!
Frank, you were too much helpful to me!! Thanks a lot !! See you around !! Stathis *********************** Patras - Greec -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult concatenate No 2
Hi
thanks for the feedback, good that this works for you :-) -- Regards Frank Kabel Frankfurt, Germany Thats all i wanted to do!! Frank, you were too much helpful to me!! Thanks a lot !! See you around !! Stathis *********************** Patras - Greece --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help, too difficult for me. | Excel Worksheet Functions | |||
Too difficult for me, please help. | Excel Worksheet Functions | |||
Difficult but do-able? | Excel Discussion (Misc queries) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
difficult concatenate macro | Excel Programming |