error 1004

[hamex]

hi, ive got a 1004 problem on below yellow line, whats wrong with this ?

Sub SplitCells()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Sheets("DATA1").Range("B" & Col).Select
For i = 1 To Selection.Rows.Count
Dim splitValues As Variant
splitValues = Split(Selection.Rows(i).Value, ",")
Selection.Rows(i).Resize(UBound(splitValues) - LBound(splitValues) + 1).Value = Application.Transpose(splitValues)
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

thanks

[shg]

Col is not initialized.

Please change your QUOTE tags to CODE tags.

Thanks.

[JBeaucaire]

You are using a variable Col for the row, and you have not given that variable a value yet.

[hamex]

as my first message in my forums and breaking the forum rules i need to say , ive download the xls file from this address : http://www.excelforum.com/excel-prog...s-to-rows.html and got this error . im really sorry for inconvience but im in hurry ,... can you help me ?

[shg]

What are you trying to do?

[hamex]

thank you for reply , i need to split the separated-comma rows (text to rows) but that gives me "Application defined or object defined error". you can see the attachment ..

thanks again

[shg]

Split them to appear where?

[hamex]

in new sheet named "DATA1" , i was download this file from john's post at "http://www.excelforum.com/excel-prog...s-to-rows.html" and modify the target content and after that on line 100 of my previous post's attachment give me the error.

[shg]

Please make this simple and post a partial example of the output as you wish it to appear.

[hamex]

No offense, can you download my attach file to see the exact problem ?

[shg]

Try replaxcing all that code with this and see if it does what you want.

Option Explicit

Sub x()
    Dim iRow        As Long
    Dim asBuf()     As String

    Worksheets("Data").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "DATA1"

    For iRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        With Cells(iRow, "A")
            If InStr(.Text, ",") Then
                asBuf = Split(Replace(.Text, " ", ""), ",")
                .Offset(1).Resize(UBound(asBuf)).EntireRow.Insert
                With .Resize(UBound(asBuf) + 1)
                    .Value = WorksheetFunction.Transpose(asBuf)
                    .Offset(, 1).Value = Cells(iRow, "B").Value
                End With
            End If
        End With
    Next iRow
    
    With ActiveSheet.UsedRange
        .Value = .Value
        .EntireColumn.AutoFit
    End With
End Sub

[hamex]

thanks shg for your time, but this one not helping me and actually i need to solve the problem not change the whole program .

[JBeaucaire]

I see the Col variable is Public in another module. Perhaps if you move the macro into that module you'll get the stored value of Col.

[jaslake]

Hi Hamex

I believe you'll find your error in this line of code

Col = Right(iCell.Offset(-lRows, 0).Address, 2)

is due to the fact that once your row number exceeds 99, the formula in the offending line of code evaluates to 0 (zero).

Change that line of code to

Col = iCell.Offset(-lRows, 0).Row

and you should be good to go.

John

PS: By the way, I tested Shg's code...it's much simpler than the code I wrote and it works wonderfully well.

[hamex]

john thank you very much for your kindly help , its ok now and the prob is solved .. God bless you

[snb]

Why not using Excel's builtin facilities ?
If the selection is restricted to cells in column B:

Sub tst()
    Selection.TextToColumns , 1, -4142, , False, False, True, False, False
End Sub

[jaslake]

Hi hamex

You're welcome. Thanks for the blessings.

John