calling a macro

M

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
Hello again

I am trying to call one macro from another however it is not running through the second macro.

The macro I am calling is:
Code: 
Sub correction()

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LR = ws.Range("A65536").End(xlUp).Row

For Each cell In ws.Range("A2:A" & LR).Cells
    If ws.Range("I2") <> "" Then
        If cell.Value <> cell.Offset(0, 6).Value Then
            ws.Range(Cells(cell.Row, "c"), Cells(cell.Row, "ab")).Insert
            ws.Range(Cells(cell.Row, "j"), Cells(cell.Row, "ab")).Value = 999
            cell.Offset(0, 6).Value = cell.Value
            cell.Offset(0, 8).Value = cell.Offset(0, 1).Value
            If ws.Range("C2") = "" Then
                cell.Offset(0, 2).Value = cell.Offset(1, 2).Value
                Else
                cell.Offset(0, 2).Value = cell.Offset(-1, 2).Value
            End If
        Else
            If cell.Offset(0, 1).Value <> cell.Offset(0, 8).Value Then
                ws.Range(Cells(cell.Row, "c"), Cells(cell.Row, "ab")).Insert
                ws.Range(Cells(cell.Row, "j"), Cells(cell.Row, "ab")).Value = 999
                cell.Offset(0, 6).Value = cell.Value
                cell.Offset(0, 8).Value = cell.Offset(0, 1).Value
                cell.Offset(0, 2).Value = cell.Offset(-1, 2).Value
                If ws.Range("C2") = "" Then
                    cell.Offset(0, 2).Value = cell.Offset(1, 2).Value
                    Else
                    cell.Offset(0, 2).Value = cell.Offset(-1, 2).Value
                End If
            End If
        End If
    End If
Next cell

ws.Range("A2:AA8785").Font.Bold = False

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

And I am trying to call it with the following section of code
Code: 
For Each ws In ActiveWorkbook.Worksheets
   ws.Range("A1:AB8785").Copy
   ws.Range("A1:AB8785").PasteSpecial Paste:=xlPasteValues
   ws.Range("C2:AB8785").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("I2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    If ws.Range("D2").Value = 0 Then
        ws.Delete
    End If
    ws.Range("A1").Select
Call correction

Next ws

I am sure I am missing something simple but I can't seem to find it anywhere.

Mark
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Mark

What makes you think the other macro isn't being called?

As far as I can see it will be called, but you'll probably have problems with ws because it isn't defined anywhere in the correction sub.

I think it would really help if you could explain in words what you are trying to do with this code.

I know you've posted on this before but I can't recall you explaining exactly what you are trying to do.

Perhaps if you posted some sample data it would help as well.:)
 
Upvote 0
Thanks for taking a look. Here is a sample of my dataSub correction()
C46004.xls
ABCDEFGHI
1DayHourSTN_IDMonthDayYearFullDateTimeCorrectedHours
201/01/20050C4600411200501/01/20050:40:000
301/01/20051C4600411200501/01/20051:40:001
401/01/20052C4600411200501/01/20052:40:002
501/01/20053C4600411200501/01/20053:40:003
601/01/20054C4600411200501/01/20054:40:004
701/01/20055C4600411200501/01/20055:40:005
801/01/20056C4600411200501/01/20056:40:006
901/01/20057C4600411200501/01/20057:40:007
1001/01/20058C4600411200501/01/20058:40:008
1101/01/20059C4600411200501/01/20059:40:009
1201/01/200510C4600411200501/01/200510:40:0010
1301/01/200511C4600411200501/01/200511:40:0011
1401/01/200512C4600411200501/01/200512:40:0012
1501/01/200513C4600411200501/01/200513:40:0013
1601/01/200514C4600411200501/01/200514:40:0014
1701/01/200515C4600411200501/01/200515:40:0015
1801/01/200516C4600411200501/01/200516:40:0016
1901/01/200517C4600411200501/01/200517:40:0017
2001/01/200518C4600411200501/01/200518:40:0018
2101/01/200519C4600413200503/01/20053:40:003
2201/01/200520C4600413200503/01/20055:40:005
2301/01/200521C4600413200503/01/200518:40:0018
2401/01/200522C4600413200503/01/200521:40:0021
2501/01/200523C4600413200503/01/200522:40:0022
2602/01/20050C4600413200503/01/200523:40:0023
2005


I have run the macro without calling it (and without "ws") and it now does exactly what I want it to do. What it is doing is looking to see if the date in column g matches that in column a and if it does not it inserts part of a row, and then adds values into some of the cells. The macro then checks that the hour in column i matches that in column b, and if it does not match it inserts part of a row and then adds values into some of the cells.

I am working with hourly data from about 15 different data loggers and the data spans almost 20 years, so it is rather large amount of data. The problem with it is that the data loggers did not always work, and would often misrecording info. This could be 1 missed hour, or several months. For my purposes I need to have full hourly data for each year. So what I have done is made columns A and B the correct hours and dates for each year that I am looking at will column C over is the actual data.

By running the macro without calling it, it does exactly what I want, so I am not concerned with it. I am just trying to find a way to run it on all the worksheets in the workbook, and I thought the best way would be to do it from another macro I am running.

Hope this explains what I am trying to do.

Mark
 
Upvote 0
Well I think I have figured out a way to make things work, but I am certain it is not the best way by far.

What I did was changed the first macro a little, mainly adding "ws.Activate"

Code: 
For Each ws In ActiveWorkbook.Worksheets
   ws.Range("A1:AB8785").Copy
   ws.Range("A1:AB8785").PasteSpecial Paste:=xlPasteValues
   ws.Range("C2:AB8785").Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("J2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    If ws.Range("E2").Value = 0 Then
        ws.Delete
    Else
        ws.Activate
        Call correction
    End If
    ws.Range("A1").Select

Next ws

And then for my second code, i changed it to be:

Code: 
Sub correction()

LR = Activesheet.Range("A65536").End(xlUp).Row

For Each cell In Activesheet.Range("A2:A" & LR).Cells
    If Activesheet.Range("J2") <> "" Then
        If cell.Value <> cell.Offset(0, 7).Value Then
            Activesheet.Range(Cells(cell.Row, "c"), Cells(cell.Row, "ac")).Insert
            Activesheet.Range(Cells(cell.Row, "k"), Cells(cell.Row, "ac")).Value = 999
            cell.Offset(0, 7).Value = cell.Value
            cell.Offset(0, 9).Value = cell.Offset(0, 1).Value
            If Activesheet.Range("D2") = "" Then
                cell.Offset(0, 3).Value = cell.Offset(1, 3).Value
                Else
                cell.Offset(0, 3).Value = cell.Offset(-1, 3).Value
            End If
        Else
            If cell.Offset(0, 1).Value <> cell.Offset(0, 9).Value Then
                Activesheet.Range(Cells(cell.Row, "c"), Cells(cell.Row, "ac")).Insert
                Activesheet.Range(Cells(cell.Row, "k"), Cells(cell.Row, "ac")).Value = 999
                cell.Offset(0, 7).Value = cell.Value
                cell.Offset(0, 9).Value = cell.Offset(0, 1).Value
                cell.Offset(0, 3).Value = cell.Offset(-1, 3).Value
                If Activesheet.Range("D2") = "" Then
                    cell.Offset(0, 3).Value = cell.Offset(1, 3).Value
                    Else
                    cell.Offset(0, 3).Value = cell.Offset(-1, 3).Value
                End If
            End If
        End If
    End If
Next cell

Activesheet.Range("A2:AC8785").Font.Bold = False

End Sub

This way works and does what I want it to, just wondering if there is a better way or just some other ways of handling calling the second macro up.

Thanks

Mark
 
Upvote 0
You must log in or register to reply here.

Similar threads

larinda4
  • Question
VBA: Send Outlook Email & Attach PDF
Replies
1
Views
104
Trebor76
T
The Ruff Report
  • Question
Add multiple attachments from email list
Replies
1
Views
269
Trebor76
T
The Ruff Report
  • Question
Help adjusting Macro to email multiple cc addresses
Replies
1
Views
365
The Ruff Report
The Ruff Report
S
  • Solved
Combine two codes in one worksheet change events
Replies
3
Views
250
Alex Blakenburg
A
Agnarr
  • Solved
Checkbox Timestamp with VBA
Replies
3
Views
193
bobsan42
bobsan42

Forum statistics

Threads
1,214,622
Messages
6,120,576
Members
448,972
Latest member
Shantanu2024

Share this page

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