concatonate

I think I need a macro ....
I would like to be able to place my cursor anywhere on the spreadsheet and
then click a macro that would concatonate all the data on the line
immediately above where the cursor is loctaed,
IE
If I place the cursor on line a14 I would want to concatonate all the data
on line 13 from a13 to e13
Need help...and thanks
PCOR

use event macro
worksheet_selectionchang
under this write your macro

"pcor" <[email protected]> wrote in message
news:[email protected]...
>I think I need a macro ....
> I would like to be able to place my cursor anywhere on the spreadsheet and
> then click a macro that would concatonate all the data on the line
> immediately above where the cursor is loctaed,
> IE
> If I place the cursor on line a14 I would want to concatonate all the
> data
> on line 13 from a13 to e13
> Need help...and thanks
> PCOR

pcor

Perhaps you can adapt this macro to suit.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


Gord Dibben MS Excel MVP


On Tue, 28 Feb 2006 16:49:28 -0800, "pcor" <[email protected]>
wrote:

>I think I need a macro ....
>I would like to be able to place my cursor anywhere on the spreadsheet and
>then click a macro that would concatonate all the data on the line
>immediately above where the cursor is loctaed,
>IE
>If I place the cursor on line a14 I would want to concatonate all the data
>on line 13 from a13 to e13
>Need help...and thanks
>PCOR

works VERY well Thanks


"Gord Dibben" wrote:

> pcor
>
> Perhaps you can adapt this macro to suit.
>
> Sub ConCat_Cells()
> Dim x As Range
> Dim y As Range
> Dim z As Range
> Dim w As String
> Dim sbuf As String
> On Error GoTo endit
> w = InputBox("Enter the Type of De-limiter Desired")
> Set z = Application.InputBox("Select Destination Cell", _
> "Destination Cell", , , , , , 8)
> Application.SendKeys "+{F8}"
> Set x = Application.InputBox("Select Cells...Contiguous or Non", _
> "Cells Selection", , , , , , 8)
> For Each y In x
> If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
> Next
> z = Left(sbuf, Len(sbuf) - 1)
> Exit Sub
> endit:
> MsgBox "Nothing Selected. Please try again."
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
>
> On Tue, 28 Feb 2006 16:49:28 -0800, "pcor" <[email protected]>
> wrote:
>
> >I think I need a macro ....
> >I would like to be able to place my cursor anywhere on the spreadsheet and
> >then click a macro that would concatonate all the data on the line
> >immediately above where the cursor is loctaed,
> >IE
> >If I place the cursor on line a14 I would want to concatonate all the data
> >on line 13 from a13 to e13
> >Need help...and thanks
> >PCOR
>
>

Happy to have been of assistance.

Gord

On Wed, 1 Mar 2006 14:14:29 -0800, "pcor" <[email protected]>
wrote:

>works VERY well Thanks
>
>
>"Gord Dibben" wrote:
>
>> pcor
>>
>> Perhaps you can adapt this macro to suit.
>>
>> Sub ConCat_Cells()
>> Dim x As Range
>> Dim y As Range
>> Dim z As Range
>> Dim w As String
>> Dim sbuf As String
>> On Error GoTo endit
>> w = InputBox("Enter the Type of De-limiter Desired")
>> Set z = Application.InputBox("Select Destination Cell", _
>> "Destination Cell", , , , , , 8)
>> Application.SendKeys "+{F8}"
>> Set x = Application.InputBox("Select Cells...Contiguous or Non", _
>> "Cells Selection", , , , , , 8)
>> For Each y In x
>> If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
>> Next
>> z = Left(sbuf, Len(sbuf) - 1)
>> Exit Sub
>> endit:
>> MsgBox "Nothing Selected. Please try again."
>> End Sub
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>> On Tue, 28 Feb 2006 16:49:28 -0800, "pcor" <[email protected]>
>> wrote:
>>
>> >I think I need a macro ....
>> >I would like to be able to place my cursor anywhere on the spreadsheet and
>> >then click a macro that would concatonate all the data on the line
>> >immediately above where the cursor is loctaed,
>> >IE
>> >If I place the cursor on line a14 I would want to concatonate all the data
>> >on line 13 from a13 to e13
>> >Need help...and thanks
>> >PCOR
>>
>>

Gord Dibben MS Excel MVP