Help With Macro Code??
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 He http://www.neilbikemexico.com |
Help With Macro Code??
Activecell
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Neil Smith" wrote in message . uk... 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 He http://www.neilbikemexico.com |
All times are GMT +1. The time now is 11:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com