pulling apart rows

[paul20k]

Hi Everyone

I am unable to attach excel file as its more than filesize limit...

I am looking for some more help regarding a macro. I am trying to use that macro on the whole column but I am unable to do that. Please take a look at the macro. Any help would be appreciated.

About macro - my macro is working fine when I select 3 rows but doesn't work when I select whole column. If you will look at the macro, you will understand it better. Just select B6, B7, B8 and run macro..it works fine according to expectaions but it splits and copies the last cell in the selected cells. I tried all options but I am unable to copy the rest of the cells on the newly created sheet.

For m = 0 To ThisCol
For j = 0 To 3
strArray = Split(Sheets(1).Cells(ThisRow + m, ThisCol).Value, vbLf)
MsgBox strArray(j)

Next j
Next m

in the section of the macro, you can see that array has all the cell info. But I am unable to see the same array output on the new sheet.

Thanks so much..I would really appreciate your help.

Note - Please select rows B6, B7, B8 and run macro to see the result.


Sub experiment()
Dim strArray() As String
Dim ThisRow As Integer
Dim ThisCol As Integer
Dim j As Integer
Dim m As Integer
Dim ws As Worksheet 'Create a worksheet object

ThisRow = ActiveWindow.RangeSelection.Row
ThisCol = ActiveWindow.RangeSelection.Column

j = ThisRow

For m = 0 To ThisCol
For j = 0 To 3
strArray = Split(Sheets(1).Cells(ThisRow + m, ThisCol).Value, vbLf)
MsgBox strArray(j)

Next j
Next m

Set ws = Sheets.Add 'set worksheet Object to new sheet
ws.Name = "New Input Data" 'rename sheet

For i = 0 To UBound(strArray)
Sheets("New Input Data").Cells(ThisRow + i, 1).Value = strArray(i)
Columns("A:A").EntireColumn.AutoFit
'MsgBox i
'MsgBox ThisRow + i
'MsgBox strArray(i)
'MsgBox UBound(strArray)
'MsgBox ThisCol
Next i

 

[GlennUK]

I am unable to attach excel file as its more than filesize limit...

errm, so this question has been posted on another forum ( that has file attachment functionality ), and now been copied here? Maybe???

You say:

Please select rows B6, B7, B8 and run macro to see the result

without telling what the contents of these cells are. What are the contents before the macro is run, and what are the contents afterwards?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Hi,

Note the comments.

You have to stick the data before parsing another row...

Sub experiment()
    Dim strArray() As String
    Dim ThisRow As Long     'get in the practice of making ROW number variables LONG, not INTEGER
    Dim ThisCol As Integer
    Dim j As Integer
    Dim m As Long
    Dim wsNew As Worksheet
    Dim wsThis As Worksheet
    
    Set wsThis = ActiveSheet
    
    ThisRow = wsThis.RangeSelection.Row
    ThisCol = wsThis.RangeSelection.Column
    
    j = ThisRow 'why do you assign a value to j and then disregard the value in the For...Next loop below???
    
    Set wsNew = Sheets.Add '
    wsNew.Name = "New Input Data"
    
    For m = 0 To ThisCol    'if m incriments a ROW, why is the LIMIT a COLUMN value???
        strArray = Split(wsThis.Cells(ThisRow + m, ThisCol).Value, vbLf) 'this was in the wrong place
        For j = 0 To UBound(strArray) 'how do you know what the limit is???
            MsgBox strArray(j)  'FYI
        Next j
    'now stick the data in the NEW sheet
        For i = 0 To UBound(strArray)
            wsNew.Cells(ThisRow + i, 1).Value = strArray(i)
            wsNew.Columns("A:A").EntireColumn.AutoFit
            'MsgBox i
            'MsgBox ThisRow + i
            'MsgBox strArray(i)
            'MsgBox UBound(strArray)
            'MsgBox ThisCol
        Next i
    
    Next m

End Sub

Skip,

_[red][/red]

 

[SkipVought]

Paul,

Your LOGIC is flawed. It appears that you are going from a cell that has MULTIPLE LINES (vbLF), to separate rows.

So m starts with ThisRow -- one row, one or more array elements. Those array elements are put in the NEW sheet, starting at ThisRow, incrimented by the array element index.

But what happens with row m+1? Your're back to ThisRow + 1 on the output. You have to start where you left off. You need a new row counter for sheet NEW.

Skip,

_[red][/red]