Calling a sub from excel sheet

[satish_j]

I found a excel sub/macro from Internet that allows a single cell contents to be split into multiple rows based on certain character.I copied the code and pasted in a new module window opened from excel(F11 key)
Now,if i go back to worksheet and enter the name of sub in a cell,it says 'That name is not valid'.I guess iam not properly calling the sub.
Can anyone help me how to call a sub from excel sheet?
Thanks..

 

[RichardMGreen]

Can you post the code and the formula you're using?

 

[niceguy_jay]

you cannot call a 'sub" from a worksheet like formula...you can call only functions or formulas...


try replacing the word "sub" with "public function"...

 

[satish_j]

This is the code i got from Net

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

    Application.ScreenUpdating = False

    For iRow = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If InStr(Cells(iRow, "B").Value, ",") Then
            astr = Split(Cells(iRow, "B"), ",")
            Rows(iRow + 1).Resize(UBound(astr)).Insert
            With Rows(iRow).Resize(UBound(astr) + 1)
                .Rows(1).Copy Destination:=.Columns(1)
                .Columns(2).Value = WorksheetFunction.Transpose(astr)
            End With
        End If
    Next iRow

    Application.ScreenUpdating = True
End Sub

I also tried replacing 'sub' with 'function' but it gives a long error on calling from sheet as:
There is a circular rerence in an open work-book.......blah...blah..
Any other ideas?

 

[niceguy_jay]

as per the macro your data that you need to split should be in column B....
have you checked with this ?? other than that the macro is fine

 

[satish_j]

as per the macro your data that you need to split should be in column B....
have you checked with this ?? other than that the macro is fine

i entered data in column B1(abc,123,104) and pressed 'enter',but iam not getting rows with each value..

 

[niceguy_jay]

dude you will need to run the macro in order to get the output...

I don't know how you used to do this earlier but now after pasting the values in B1, go the the code in VBA editor and press F5 or the Run button on the menu bar and your work will be done

 

[GTO]

FWIW, I would tell us what it is you'd like to do and where (what module) you plunked this into?

 

[satish_j]

dude you will need to run the macro in order to get the output...

I don't know how you used to do this earlier but now after pasting the values in B1, go the the code in VBA editor and press F5 or the Run button on the menu bar and your work will be done

Perfect,it worked gr8..sorry,iam newbie to macros..
can you help me change the character used in Sub from comma(,) to angle bracket(>)
Nevermind,i got it..

 

[niceguy_jay]

over here in the code

astr = Split(Cells(iRow, "B"), ",")

replace the "," with ">" or any other character as u like....you can even put a word over there