LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 17th 06, 02:38 PM posted to microsoft.public.excel.misc
external usenet poster
First recorded activity by ExcelBanter: Jul 2006
Posts: 59
Default Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A"

Using 2003

Currently, I insert the following VBA formula which is entered manually

into B1. Note: In the case below, I am must change the formula by
adding &" "&OFFSET(Bx,y,0) for the number of cells in Column A (from
row 2 through row 7) restarting every time there is a new value (not
empty) in column A.

Hopefully, my columns line up close enough. I am entering this via
"Google Groups"

=B2&" "&OFFSET(B2,1,0)&" "&OFFSET(B2,2,0)&" "&OFFSET(B2,3,0)&"
"&OFFSET(B2,4,0)&" "&OFFSET(B2,5,0)&" "&OFFSET(B2,6,0)&"
"&OFFSET(B2,7,0)&" "&OFFSET(B2,8,0)&" "&OFFSET(B2,9,0
************************************************** *********************


2 "Example 1" I The
3 love time
4 working is
5 in VBA 2:00 PM

7 daily
8 "Example 2"
Stop when the data ends


Note: like in Col B, there could be a blank cell in Col B
Note: I use B1 as a helper row (maybe I do not need to??)

When finished:

1 Example 1 I love working The time is 2:00PM
in VBA daily
2 Example 2 Stop when the data ends

Note: rows 1-my helper row, 3, 4, 5, 6, 7 & 9) have been
Note: the change in data in Col A restarts the loop
Note: when all data in the Used-range** is processed, the macro
should stop

** (Note: quite often the new user in Excel will copy formating
all the way to 65536 and the "Used Range" is huge. How can I
redefine Used-Range as Range with real data not just formatting?)
************************************************** *************************************

The need for a VBA solution, is that many use Excel as a note pad when
they type in values in a column row after row as the cell fills up. If
they just
used "Text-wrap", they would have placed the all of the data in only
one cell.

I would like to have an option (MsgBox?) to start processing at Row ??
(?? via Msg box) or for the Used range below the start row.

Thanks Dennis

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro question Chris Excel Worksheet Functions 12 July 7th 06 01:23 AM
Pulling out data based on font colour terryc Excel Discussion (Misc queries) 1 July 3rd 06 09:51 PM
Inserting a new line when external data changes Rental Man Excel Discussion (Misc queries) 0 January 11th 06 07:05 PM
Macro does not run when data refreshed Larry Lehman Excel Discussion (Misc queries) 0 January 16th 05 07:31 PM
Daily Macro to Download Data, Order and paste in order Iarla Excel Worksheet Functions 1 November 17th 04 01:59 PM

All times are GMT +1. The time now is 04:21 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"


Copyright © 2017