Splitting of Cell by Change of Value using VBA

[nkms143]

hi...

i using the following code to split cells in Column A depending upon number of cells containing text or numbers separated by delimiter "-". It works fine. But when i tried this code in a worksheet after changing the value, it gets runtime error '1004' - Application defined or object defined error. I need Why it is not working. The code is attached to sheet1 and it should execute when a value changes in any of the cell in Column A.
Thanks in Advance for Valuable Suggestion.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim rwCount As Long
    Dim i As Long
    Dim vA As Variant
    rwCount = Application.WorksheetFunction.CountA(Sheet1.Range("A1:A" & Rows.Count))
        For i = 1 To rwCount
            vA = Split(Selection.Resize(1).Offset(i - 1), "-")
            Selection.Offset(i - 1).Resize(1, UBound(vA) + 1).Offset(, 1) = vA
        Next
End Sub

 

[Luke M]

Your For loop tells the code to loop from 1 to # of cells with text. However, you use commands with Selection as the object, which is not necessariy the first/last item in your list.
Also, note that since this is the Change event, every time the code makes a change, it fires the event, creating a multi-nested chain of event macros!

Here's my proposed re-write

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vA As Variant
   
    'Check first if we're changing a cell we care about
    'We also don't care if multiple cells changed (for now)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
   
   
    'Turn off future events temporarily
    Application.EnableEvents = False
    vA = Split(Target.Value, "-")
    Target.Resize(1, UBound(vA) + 1) = vA
    'Turn events back on
    Application.EnableEvents = True
End Sub

 

[nkms143]

Thanks sir, for your valuable suggestions. It indeed worked. However, the code is not working second time (although working manually), when a dependent cell is linked with Column A and the dependent cell is changed second time.
Any suggestions will be hearty appreciated.

 

[Luke M]

Could you send me an example of your layout? I'm having trouble understanding what you mean.

 

[nkms143]

Thankq very much for ur response. but i manged with few alteration in codes. it works fines. thank you very much for your suggestions.