Urgent help!!!! VBA  
Author Message
sunilj20





PostPosted: Thu Dec 11 19:51:24 CST 2003 Top

Excel Programming >> Urgent help!!!! VBA

If the cell(1,1) has data of several lines
eg:
Hello World
Hello World
Hello World
Hello World
Hello World

How can I split this data into several rows using VBA?

The steps to do the same manually are
1. Select the data in cell(1,1)
2. Cut it
3. select cell(2,1) and select back cell(1,1)( to resize the cell1)
4. Paste the data in the buffer and excel automatically pastes each
line into a different row.

How do I perform the same using VBA, if I try to record the actions
described above , excel is not recording any statements for steps 1 and
2.

I appreciate any help..


---
Message posted from http://www.hide-link.com/

Excel191  
 
 
Gord





PostPosted: Thu Dec 11 19:51:24 CST 2003 Top

Excel Programming >> Urgent help!!!! VBA Problem with recording these steps is that steps 1 and 2 are done in Edit
mode. VBA macro will not record or work while in Edit Mode.

You could record splitting the data into 5 columns using the Text to Columns
Wizard then transposing to a column.

Line-break character(other) in Text Wizard De-limiter is usually ALT + 010 on
Numpad.

Hang in there. One of the VBA gurus will be along with something easier and
more elegant.

Gord Dibben Excel MVP

On Thu, 11 Dec 2003 18:40:47 -0600, pswarna


>If the cell(1,1) has data of several lines
>eg:
>Hello World
>Hello World
>Hello World
>Hello World
>Hello World
>
>How can I split this data into several rows using VBA?
>
>The steps to do the same manually are
>1. Select the data in cell(1,1)
>2. Cut it
>3. select cell(2,1) and select back cell(1,1)( to resize the cell1)
>4. Paste the data in the buffer and excel automatically pastes each
>line into a different row.
>
>How do I perform the same using VBA, if I try to record the actions
>described above , excel is not recording any statements for steps 1 and
>2.
>
>I appreciate any help..
>
>
>---
>Message posted from http://www.ExcelForum.com/

 
 
Ron





PostPosted: Thu Dec 11 20:11:32 CST 2003 Top

Excel Programming >> Urgent help!!!! VBA On Thu, 11 Dec 2003 18:40:47 -0600, pswarna


>If the cell(1,1) has data of several lines
>eg:
>Hello World
>Hello World
>Hello World
>Hello World
>Hello World
>
>How can I split this data into several rows using VBA?
>
>The steps to do the same manually are
>1. Select the data in cell(1,1)
>2. Cut it
>3. select cell(2,1) and select back cell(1,1)( to resize the cell1)
>4. Paste the data in the buffer and excel automatically pastes each
>line into a different row.
>
>How do I perform the same using VBA, if I try to record the actions
>described above , excel is not recording any statements for steps 1 and
>2.
>
>I appreciate any help..


Assuming there is a Line Feed between each Hello World (as there is in your
post), and also if you have a later version of VBA, then the following VBA
routine will work:

========================
Option Explicit
Sub SplitCell()
Dim temp As Variant
Dim i As Integer

temp = Split(Cells(1, 1), vbLf)

For i = 0 To UBound(temp)
Cells(i + 1, 1) = temp(i)
Next i
End Sub
====================

>
>
>---
>Message posted from http://www.ExcelForum.com/

--ron