CODEWITHSUNDEEP
vbaexcel
user9730643
user9730643

Reputation: 89

Script not parsing cells when called in another Sub , excel Vba

I have a script that runs through a column in a sheet called data and parses the cell length to cut down the length. This script works when placed in a module and run on the sheet. However the issue I am having is that when I call it in my overall script. Does anyone have an idea why it is being run but not working?

This is the Sub

Sub Remove10()
Dim i As Long
Dim lr As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")

lr = ws.Cells(ws.Rows.Count, "T").End(xlUp).Row

For i = 2 To lr
    If Len(ThisWorkbook.Sheets("Data").Cells(i, 11).Value) > 8 Then Cells(i, 11).Value = Right(Cells(i, 11), 8)

Next i
End Sub

And this is where it is called

Private Sub RunFullAutomation_Click()
'----------------------------------------------------------------------------------------------
'This is the action that is taken when the "GO!" Button is pressed.
'Runs the following three subs in order without further need for user input.
'----------------------------------------------------------------------------------------------
'Get the data and set up the sheets
Call GetAndSetUpData
Call Remove10
Call RemoveYearsBefore18

Call RemoveYearsBefore16
'Data fixing
Call removeCancelledDescrepency
'report generation
Call FindExceptions
'Notify the user that the automation is all done
Range("CurrentTask").Value = "Complete"
End Sub

Upvotes: 1

Views: 31

Answers (1)

Vityata
Vityata

Reputation: 43575

This is the probably the most common VBA error in StackOveflow (from experience):

Sub Remove10()
    Dim i As Long
    Dim lr As Long

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    lr = ws.Cells(ws.Rows.Count, "T").End(xlUp).Row

    For i = 2 To lr
        If Len(ws.Cells(i, 11)) > 8 Then ws.Cells(i, 11).Value = Right(ws.Cells(i, 11), 8)
    Next i
End Sub

The problem is that if you do not refer to the explicit Worksheet of the Cell it uses the ActiveSheet. Thus, if the Range() gets broken, trying to get cells from different cells.

Upvotes: 1

Related Questions