VBA code doesn't run when I call it automatically

Question

I have a macro named Splittext that is called when there is a change in cell "B4" of sheet Macro Process it doesn't work when I call it but it works when I manually run it. There is no error in the code

Sub splitText()
    Dim wsS1 As Worksheet 'Sheet1
    Dim textstring As String, warray() As String, counter As Integer, strg As String

    Set wsS1 = Sheets("OUTPUT 1")
    wsS1.Activate

    textstring = Range("A2").Value
    warray() = Split(textstring, ">")

    For counter = LBound(warray) To UBound(warray)
        strg = warray(counter)
        Cells(counter + 3, 1).Value = Trim(strg)
    Next counter

    textstring = Range("B2").Value
    warray() = Split(textstring, ">")

    For counter = LBound(warray) To UBound(warray)
        strg = warray(counter)
        Cells(counter + 3, 2).Value = Trim(strg)  
    Next counter

    textstring = Range("C2").Value
    warray() = Split(textstring, ">")

    For counter = LBound(warray) To UBound(warray)
        strg = warray(counter)
        Cells(counter + 3, 3).Value = Trim(strg)
    Next counter
End Sub

This code is supposed to separate the text present in the Cells ("A2")("B2")("C2") of sheet "OUTPUT 1"

This is how I am calling the code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set Target = Range("B4")
    If Target.Value = "Completed" Then
        Call splitText
    End If
End Sub

@TimWilliams please check the question i've updated it with the code that i am using to call the macro — j.doe
– j.doe, Commented Jan 23, 2019 at 5:58
None of your code refers to specific worksheets - a good start would be to qualify all of your range/cells references so they refer to the correct worksheet. Which sheet are you monitoring for changes? All of them? — Tim Williams
– Tim Williams, Commented Jan 23, 2019 at 6:01
FWIW You have code that is essentially repeating at the top which, I think, with another outer loop from 1 to 3 could reduce the inner looping to just one block. For i = 1 to 3: textstring = Cells(2,i).Value : Cells(counter + 3, i) '<== key replacement lines — QHarr
– QHarr, Commented Jan 23, 2019 at 6:02
you mean i need to define a worksheet on which the code needs to run??? @TimWilliams — j.doe
– j.doe, Commented Jan 23, 2019 at 6:02

Tim Williams · Accepted Answer · 2019-01-23 06:25:16Z

1

It's unclear what sheet you're monitoring for changes, but this worked for me:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set Target = sh.Range("B4")
    If Target.Value = "Completed" Then
        Application.EnableEvents = False
        splitText
        Application.EnableEvents = True
    End If
End Sub


Sub splitText()
    Dim warray() As String, i As Long, c As Range
    For Each c In ThisWorkbook.Sheets("OUTPUT 1").Range("A2:C2").Cells
        warray = Split(c.Value, ">")
        For i = LBound(warray) To UBound(warray)
            c.Offset(i + 1, 0).Value = Trim(warray(i))
        Next i
    Next c
End Sub

answered Jan 23, 2019 at 6:25

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

j.doe Over a year ago

finally ...Thanks! ...it worked ..Thank you so much for your patience and help!

AJD Over a year ago

Tim, I know it was in the OP - but why have a SheetChange and then set Target to a fixed address that is likely to be anything but the target? I am really surprised you did not pick this up! (Instead: If Target.Address = "B4" etc).

Tim Williams Over a year ago

I did pick it up but there's some potential reasons for why it might be that way so...

AJD Over a year ago

Tim, from OP "that is called when there is a change in cell "B4" of sheet " - so the event handler should check to see if that is the cell that is changed - not force the issue. The only thing I can think of is if B4 is changed through formula but then some other criteria must be checked to see if there really is a change. The code as currently stands will run every time there is a change in any worksheet as long as B4 = completed.

Tim Williams Over a year ago

The comment above the code was a hint for the OP that they had a bit of work to do to make it right. B4 is right under the cells being split, and the process would quite likely overwrite whatever is in B4...

Collectives™ on Stack Overflow

VBA code doesn't run when I call it automatically

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related