Help With Macro Code??

[Neil Smith]

Hi there,

I got this question at work - just wondering if anyone can help me out - i
know the logic, just not the code

Im guessing i need to find the cell the mouse is in before i click my
button, then set a variable to this, which will replce the reference to cell
"A3" - code follows:

thanks again....

I have recorded an Excel macro to convert text to columns (using the

Data/Text to Columns command):

Sub text_to_columns()
'
' text_to_columns Macro
' Macro recorded 24/08/2006 by mark.holliday
'

'
Selection.TextToColumns Destination:=Range("A3"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(7, 1), Array(18,
1), Array(29, 1), _
Array(39, 1), Array(51, 1), Array(61, 1), Array(78, 1))
End Sub


I have assigned a toolbar button to the macro, and I want to use it to
copy many large blocks of data in text format from the laboratory computer
and put them into columns in Excel. This entails pasting blocks of data
side by side into an Excel worksheet, and using the Text-to-Columns
command each time.

When I recorded the macro, the active cell happened to be A3. When I run
the macro, it always overwrites data into columns starting from cell A3,
irrespective of where I initially paste the text-format data.

How should the macro be written so that the column-reformatted data is
inserted in a block whose top left cell is defined by the currently
selected cell? ie. no overwriting; so that the macro automatically resets
the destination range depending on the currently selected cell?

--
________________________________________________________________

Sponsor me on my Mexico Bike Challenge!
In aid of Cerebral Palsy with Scope
14th - 26th April 2007
Donate Here: http://www.neilbikemexico.com

 

[Bob Phillips]

Activecell

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)