Help! sub that calls other subs not working, if individually run they work!

Vassilisr

New Member
Joined
Nov 18, 2014
Messages
6
Hello everyone

would really need some advise with a weird thing going on

I have written some 12 subs, all needed to process a dataset. each one takes it from where the previous one leaves it.
when i run them individually and with the right order all work fine

when I ran the general Sub, which includes basically 12 Calls (the subs mentioned above) it doesn't process the data correctly. the problem is the 4th code. If I run individually and after it finishes continue with the rest, all is good

things that could be useful to know is that
i have not defined any general variants, all are defined inside their respective subs
At start, I use a prompt window to make the user enter specific info required
I have placed all codes at one simple module

sorry for not sending the code, but it would not be useful as it is really huge (2,000 lines)

thank you!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Where does the data set come from? Do you use any external queries?

FWIW, depending on the answer to that, the code would probably be extremely useful - especially the part that doesn't work properly... ;)
 
Upvote 0
Are the subs all in the same module?
Are any of them Private?
Step through the 4th sub and see where the problem occurs then post the code in that area.
 
Upvote 0
I run a query and download the data at an exel, then I copy/paste them to the excel that has the macros...the form of the data are identical each time, just the numbers change

All subs are in the same module...not private or public (actually i do not know when private/public is :) )

when I run the 4th individually it works, if I step into it again it works great, so the Sub itself has no errors

I have placed a button at the excel connected to the aggregate sub (with all the calls in it)...so when all subs run automatically one after another, the 4th miscalculates
 
Upvote 0
Actually, this is the Sub..it examines the data from one sheet and copies it to another but at a different form, based on criteria


Code:
Sub Copy_data_v3()
'copies raw data to new sheets horizontally

Dim FirstR As Long
Dim LastR As Long
Dim LRow As Long
Dim LCol As Long
Dim j3 As Long
Dim n As Long
Dim Temp_n As Long


'select first sheet
Worksheets(2).Select


LRow = Cells(Rows.Count, 1).End(xlUp).Row
LCol = 34
    'Debug.Print LRow
    'Debug.Print LCol


Temp_n = 1


'Loop to find rows of each strategy
    For j3 = 1 To LRow
    
    Range("C" & j3).Select
        Selection.End(xlDown).Select
            FirstR = ActiveCell.Row
            
        Selection.End(xlDown).Select
            LastR = ActiveCell.Row
    
'if to deal with one-line strategies
    If Cells(LastR, 3).Value <> Cells(FirstR, 3).Value Then
    LastR = FirstR
    End If


'if to stop the code from scanning the whole sheet
    If LastR > LRow Then
    LastR = LRow
    End If
    
    'Debug.Print FirstR
    'Debug.Print LastR


'for to check each line of a specific strategy

    For n = FirstR To LastR
        Cells(n, 1).Select
        If Cells(n, 1).Value = "Purchase" Then
            Cells(n, 3).Copy
            Worksheets(3).Cells(n + 2, 2).PasteSpecial
            Cells(n, 5).Copy
            Worksheets(3).Cells(n + 2, 5).PasteSpecial
            Cells(n, 7).Copy
            Worksheets(3).Cells(n + 2, 6).PasteSpecial
            Cells(n, 18).Copy
            Worksheets(3).Cells(n + 2, 7).PasteSpecial
            Cells(n, 13).Copy
            Worksheets(3).Cells(n + 2, 8).PasteSpecial
            Cells(n, 14).Copy
            Worksheets(3).Cells(n + 2, 9).PasteSpecial
            Cells(n, 8).Copy
            Worksheets(3).Cells(n + 2, 10).PasteSpecial
            Cells(n, 9).Copy
            Worksheets(3).Cells(n + 2, 11).PasteSpecial
            Cells(n, 10).Copy
            Worksheets(3).Cells(n + 2, 12).PasteSpecial
            Cells(n, 11).Copy
            Worksheets(3).Cells(n + 2, 13).PasteSpecial
            Cells(n, 15).Copy
            Worksheets(3).Cells(n + 2, 33).PasteSpecial
            Cells(n, 16).Copy
            Worksheets(3).Cells(n + 2, 34).PasteSpecial
            Cells(n, 23).Copy
            Worksheets(3).Cells(n + 2, 35).PasteSpecial
            Temp_n = n
        Else
        If Cells(n, 1).Value = "Sale" Then
            Cells(n, 3).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
            Cells(n, 14).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 14).PasteSpecial
            Cells(n, 5).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 15).PasteSpecial
            Cells(n, 7).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 16).PasteSpecial
            Cells(n, 13).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 17).PasteSpecial
            Cells(n, 8).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 18).PasteSpecial
            Cells(n, 9).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 19).PasteSpecial
            Cells(n, 10).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 20).PasteSpecial
            Cells(n, 11).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 21).PasteSpecial
            Cells(n, 23).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 36).PasteSpecial
                If Cells(n + 1, 1).Value <> "Sale" Then
                Temp_n = n
                End If
        Else
        If Cells(n, 1).Value = "Adjustment" Then
            Cells(n, 3).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
            Cells(n, 11).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 24).PasteSpecial
                If Cells(n + 1, 1).Value <> "Adjustment" Then
                Temp_n = n
                End If
        Else
        If Cells(n, 1).Value = "Hed" Then
            Cells(n, 3).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
            Cells(n, 7).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 28).PasteSpecial
            Cells(n, 8).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 29).PasteSpecial
            Cells(n, 9).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 30).PasteSpecial
            Cells(n, 10).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 31).PasteSpecial
            Cells(n, 11).Copy
            Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 32).PasteSpecial
                If Cells(n + 1, 1).Value <> "Hed" Then
                Temp_n = n
                End If
        Else
        If Cells(n, 1).Value = "Cost" Then
                If Cells(n, 5).Value = "Dem" Then
                Cells(n, 3).Copy
                Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
                Cells(n, 11).Copy
                Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 23).PasteSpecial
                Else
                If Cells(n, 14).Value = "Sol" Then
                Cells(n, 3).Copy
                Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
                Cells(n, 11).Copy
                Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 22).PasteSpecial
                Else
                Cells(n, 3).Copy
                Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
                Cells(n, 5).Copy
                Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 25).PasteSpecial
                Cells(n, 11).Copy
                Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 26).PasteSpecial
                Cells(n, 14).Copy
                Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 27).PasteSpecial
                End If
                End If
                    If Cells(n + 1, 1).Value <> "Cost" Then
                    Temp_n = n
                    End If
        End If
        End If
        End If
        End If
        End If
        
        
        Next n


j3 = LastR
Next j3


End Sub
 
Last edited by a moderator:
Upvote 0
I run a query and download the data at an exel, then I copy/paste them to the excel that has the macros...the form of the data are identical each time, just the numbers change

So the macros are not run until after the data has already been pasted successfully into the workbook?
 
Upvote 0
And what exactly goes wrong when you just run it?
 
Upvote 0
it is supposed to take each line of data from the 1st sheet (some 4k lines) and copy them appropriately at the 2nd sheet.

but for some reason it only takes the 1st line of data, copies it and then exits and continues with the next subs

if i run the first three subs, then this one on its own, and then the remaining eight, all works fine! which is strange
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top