Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Call up a Macro within another Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Call up a Macro within another Macro
I am trying to run a simple sort macro (this is called SORT) which I have
recorded, within another macro. I have typed the word SORT in a line within
my main macro thinking that this would activate the sort macro at that
point, but I'm getting the following error message at the SORT part, Compile
Error: expected variable or procedure not Module

My full macro is as follows

Public Sub PostToAccounts()

Sort

Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With



strSheetName = Worksheets(ActiveSheet.Name).Range("D4")

Sheets.Add
ActiveSheet.Name = strSheetName & "_Acc"
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Input")

lngPosY = 9 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "DOCUMENT_TYPE"
Range("D1") = "DOCUMENT_YEAR"
Range("E1") = "DOCUMENT_PERIOD"
Range("F1") = "DOCUMENT_DATE"
Range("G1") = "Nominal"
Range("H1") = "Subaccount"
Range("I1") = "Level3"
Range("J1") = "Document_Value"
Range("K1") = "Description"
Range("L1") = "JR_DATE"
Range("M1") = "JR_YEAR"
Range("N1") = "JR_PERIOD"

Range("A2") = "1" ' The document number
Range("B2") = "1"
Range("C2") = "CLAD"
Range("D2") = Worksheets("Input").Range("AC3")
Range("E2") = Worksheets("Input").Range("AC4")
Range("F2") = Worksheets("Input").Range("AC5")
Range("N2") = Worksheets("Input").Range("AC2")
Range("L2") = Worksheets("Input").Range("AC5")
Range("M2") = Worksheets("Input").Range("AC1")

lngOutY = 3

Do While Len(Worksheets("Input").Range("aa" & lngPosY)) 0
Range("A" & lngOutY) = "1" ' The document number
Range("B" & lngOutY) = lngOutY - 1
Range("C" & lngOutY) = "CLAD"
Range("D" & lngOutY) = Worksheets("Input").Range("AC3")
Range("E" & lngOutY) = Worksheets("Input").Range("AC4")
Range("F" & lngOutY) = Worksheets("Input").Range("AC5")
Range("G" & lngOutY) = Worksheets("Input").Range("aa" & lngPosY)
Range("H" & lngOutY) = Worksheets("Input").Range("ab" & lngPosY)
Range("I" & lngOutY) = Worksheets("Input").Range("ac" & lngPosY)
If Worksheets("Input").Range("AF" & lngPosY) 0 Then
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AF" &
lngPosY), 2)
Else
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AG" &
lngPosY) * -1, 2)
End If
Range("K" & lngOutY) = Worksheets("Input").Range("AD" & lngPosY)

lngPosY = lngPosY + 1
lngOutY = lngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
Columns("D:E").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
Selection.NumberFormat = "M/D/YYYY"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:I").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "0.00"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "M/D/YYYY"
Columns("M:N").Select
Selection.NumberFormat = "0"
Cells.Select
Cells.EntireColumn.AutoFit

For i = Range("B700").End(xlUp).Row To 2 Step -1
If Left(Range("B" & i).Value, 3) < "1" Then
If Range("J" & i).Value = 0 Then
Range("J" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=(B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b700").End(xlUp).Row)
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Range("A2").Select

MsgBox "Sheet Converted - Now Post To Accounts"


End Sub


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default Call up a Macro within another Macro
John,

I believe that's because Sort is a reserved word in Excel VB, actually a
method; change the macro name (and the call) to something different, and it
will be called correctly.

HTH,
Nikos

"John" wrote in message
...
I am trying to run a simple sort macro (this is called SORT) which I have
recorded, within another macro. I have typed the word SORT in a line
within
my main macro thinking that this would activate the sort macro at that
point, but I'm getting the following error message at the SORT part,
Compile
Error: expected variable or procedure not Module

My full macro is as follows

Public Sub PostToAccounts()

Sort

Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With



strSheetName = Worksheets(ActiveSheet.Name).Range("D4")

Sheets.Add
ActiveSheet.Name = strSheetName & "_Acc"
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Input")

lngPosY = 9 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "DOCUMENT_TYPE"
Range("D1") = "DOCUMENT_YEAR"
Range("E1") = "DOCUMENT_PERIOD"
Range("F1") = "DOCUMENT_DATE"
Range("G1") = "Nominal"
Range("H1") = "Subaccount"
Range("I1") = "Level3"
Range("J1") = "Document_Value"
Range("K1") = "Description"
Range("L1") = "JR_DATE"
Range("M1") = "JR_YEAR"
Range("N1") = "JR_PERIOD"

Range("A2") = "1" ' The document number
Range("B2") = "1"
Range("C2") = "CLAD"
Range("D2") = Worksheets("Input").Range("AC3")
Range("E2") = Worksheets("Input").Range("AC4")
Range("F2") = Worksheets("Input").Range("AC5")
Range("N2") = Worksheets("Input").Range("AC2")
Range("L2") = Worksheets("Input").Range("AC5")
Range("M2") = Worksheets("Input").Range("AC1")

lngOutY = 3

Do While Len(Worksheets("Input").Range("aa" & lngPosY)) 0
Range("A" & lngOutY) = "1" ' The document number
Range("B" & lngOutY) = lngOutY - 1
Range("C" & lngOutY) = "CLAD"
Range("D" & lngOutY) = Worksheets("Input").Range("AC3")
Range("E" & lngOutY) = Worksheets("Input").Range("AC4")
Range("F" & lngOutY) = Worksheets("Input").Range("AC5")
Range("G" & lngOutY) = Worksheets("Input").Range("aa" & lngPosY)
Range("H" & lngOutY) = Worksheets("Input").Range("ab" & lngPosY)
Range("I" & lngOutY) = Worksheets("Input").Range("ac" & lngPosY)
If Worksheets("Input").Range("AF" & lngPosY) 0 Then
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AF" &
lngPosY), 2)
Else
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AG" &
lngPosY) * -1, 2)
End If
Range("K" & lngOutY) = Worksheets("Input").Range("AD" & lngPosY)

lngPosY = lngPosY + 1
lngOutY = lngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
Columns("D:E").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
Selection.NumberFormat = "M/D/YYYY"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:I").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "0.00"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "M/D/YYYY"
Columns("M:N").Select
Selection.NumberFormat = "0"
Cells.Select
Cells.EntireColumn.AutoFit

For i = Range("B700").End(xlUp).Row To 2 Step -1
If Left(Range("B" & i).Value, 3) < "1" Then
If Range("J" & i).Value = 0 Then
Range("J" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=(B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b700").End(xlUp).Row)
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Range("A2").Select

MsgBox "Sheet Converted - Now Post To Accounts"


End Sub




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,236
Default Call up a Macro within another Macro
Possibly you are confusing a module with a macro.

A macro is a section of code starting with the word Sub or Function (it can
also be Public Sub or Public Function)

macros live in modules

You see modules in your Project Explorer window.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"John" wrote in message
...
I am trying to run a simple sort macro (this is called SORT) which I have
recorded, within another macro. I have typed the word SORT in a line
within
my main macro thinking that this would activate the sort macro at that
point, but I'm getting the following error message at the SORT part,
Compile
Error: expected variable or procedure not Module

My full macro is as follows

Public Sub PostToAccounts()

Sort

Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With



strSheetName = Worksheets(ActiveSheet.Name).Range("D4")

Sheets.Add
ActiveSheet.Name = strSheetName & "_Acc"
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Input")

lngPosY = 9 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "DOCUMENT_TYPE"
Range("D1") = "DOCUMENT_YEAR"
Range("E1") = "DOCUMENT_PERIOD"
Range("F1") = "DOCUMENT_DATE"
Range("G1") = "Nominal"
Range("H1") = "Subaccount"
Range("I1") = "Level3"
Range("J1") = "Document_Value"
Range("K1") = "Description"
Range("L1") = "JR_DATE"
Range("M1") = "JR_YEAR"
Range("N1") = "JR_PERIOD"

Range("A2") = "1" ' The document number
Range("B2") = "1"
Range("C2") = "CLAD"
Range("D2") = Worksheets("Input").Range("AC3")
Range("E2") = Worksheets("Input").Range("AC4")
Range("F2") = Worksheets("Input").Range("AC5")
Range("N2") = Worksheets("Input").Range("AC2")
Range("L2") = Worksheets("Input").Range("AC5")
Range("M2") = Worksheets("Input").Range("AC1")

lngOutY = 3

Do While Len(Worksheets("Input").Range("aa" & lngPosY)) 0
Range("A" & lngOutY) = "1" ' The document number
Range("B" & lngOutY) = lngOutY - 1
Range("C" & lngOutY) = "CLAD"
Range("D" & lngOutY) = Worksheets("Input").Range("AC3")
Range("E" & lngOutY) = Worksheets("Input").Range("AC4")
Range("F" & lngOutY) = Worksheets("Input").Range("AC5")
Range("G" & lngOutY) = Worksheets("Input").Range("aa" & lngPosY)
Range("H" & lngOutY) = Worksheets("Input").Range("ab" & lngPosY)
Range("I" & lngOutY) = Worksheets("Input").Range("ac" & lngPosY)
If Worksheets("Input").Range("AF" & lngPosY) 0 Then
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AF" &
lngPosY), 2)
Else
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AG" &
lngPosY) * -1, 2)
End If
Range("K" & lngOutY) = Worksheets("Input").Range("AD" & lngPosY)

lngPosY = lngPosY + 1
lngOutY = lngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
Columns("D:E").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
Selection.NumberFormat = "M/D/YYYY"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:I").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "0.00"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "M/D/YYYY"
Columns("M:N").Select
Selection.NumberFormat = "0"
Cells.Select
Cells.EntireColumn.AutoFit

For i = Range("B700").End(xlUp).Row To 2 Step -1
If Left(Range("B" & i).Value, 3) < "1" Then
If Range("J" & i).Value = 0 Then
Range("J" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=(B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b700").End(xlUp).Row)
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Range("A2").Select

MsgBox "Sheet Converted - Now Post To Accounts"


End Sub




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,337
Default Call up a Macro within another Macro
Not necessarily true but you are correct in that it is best to name SortJoe
or something else. Try this

Sub checksort()
MsgBox "hi"
sort
MsgBox "low"
End Sub

Sub sort()
MsgBox "Will do"
End Sub
--
Don Guillett
SalesAid Software

"Nikos Yannacopoulos" wrote in message
...
John,

I believe that's because Sort is a reserved word in Excel VB, actually a
method; change the macro name (and the call) to something different, and
it
will be called correctly.

HTH,
Nikos

"John" wrote in message
...
I am trying to run a simple sort macro (this is called SORT) which I
have
recorded, within another macro. I have typed the word SORT in a line
within
my main macro thinking that this would activate the sort macro at that
point, but I'm getting the following error message at the SORT part,
Compile
Error: expected variable or procedure not Module

My full macro is as follows

Public Sub PostToAccounts()

Sort

Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With



strSheetName = Worksheets(ActiveSheet.Name).Range("D4")

Sheets.Add
ActiveSheet.Name = strSheetName & "_Acc"
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Input")

lngPosY = 9 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "DOCUMENT_TYPE"
Range("D1") = "DOCUMENT_YEAR"
Range("E1") = "DOCUMENT_PERIOD"
Range("F1") = "DOCUMENT_DATE"
Range("G1") = "Nominal"
Range("H1") = "Subaccount"
Range("I1") = "Level3"
Range("J1") = "Document_Value"
Range("K1") = "Description"
Range("L1") = "JR_DATE"
Range("M1") = "JR_YEAR"
Range("N1") = "JR_PERIOD"

Range("A2") = "1" ' The document number
Range("B2") = "1"
Range("C2") = "CLAD"
Range("D2") = Worksheets("Input").Range("AC3")
Range("E2") = Worksheets("Input").Range("AC4")
Range("F2") = Worksheets("Input").Range("AC5")
Range("N2") = Worksheets("Input").Range("AC2")
Range("L2") = Worksheets("Input").Range("AC5")
Range("M2") = Worksheets("Input").Range("AC1")

lngOutY = 3

Do While Len(Worksheets("Input").Range("aa" & lngPosY)) 0
Range("A" & lngOutY) = "1" ' The document number
Range("B" & lngOutY) = lngOutY - 1
Range("C" & lngOutY) = "CLAD"
Range("D" & lngOutY) = Worksheets("Input").Range("AC3")
Range("E" & lngOutY) = Worksheets("Input").Range("AC4")
Range("F" & lngOutY) = Worksheets("Input").Range("AC5")
Range("G" & lngOutY) = Worksheets("Input").Range("aa" & lngPosY)
Range("H" & lngOutY) = Worksheets("Input").Range("ab" & lngPosY)
Range("I" & lngOutY) = Worksheets("Input").Range("ac" & lngPosY)
If Worksheets("Input").Range("AF" & lngPosY) 0 Then
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AF"
&
lngPosY), 2)
Else
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AG"
&
lngPosY) * -1, 2)
End If
Range("K" & lngOutY) = Worksheets("Input").Range("AD" & lngPosY)

lngPosY = lngPosY + 1
lngOutY = lngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
Columns("D:E").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
Selection.NumberFormat = "M/D/YYYY"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:I").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "0.00"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "M/D/YYYY"
Columns("M:N").Select
Selection.NumberFormat = "0"
Cells.Select
Cells.EntireColumn.AutoFit

For i = Range("B700").End(xlUp).Row To 2 Step -1
If Left(Range("B" & i).Value, 3) < "1" Then
If Range("J" & i).Value = 0 Then
Range("J" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=(B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b700").End(xlUp).Row)
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Range("A2").Select

MsgBox "Sheet Converted - Now Post To Accounts"


End Sub






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,337
Default Call up a Macro within another Macro
It might be that your sort macro is PRIVATE.
You should also try to remove as many selections as possible. Example.

with Rows("1:1")
.Font.Bold = True
.Font.ColorIndex = 6
.Interior.ColorIndex = 49
End With

Columns("A:B").NumberFormat = "0"
Columns("C:C").NumberFormat = "@"

--
Don Guillett
SalesAid Software

"John" wrote in message
...
I am trying to run a simple sort macro (this is called SORT) which I have
recorded, within another macro. I have typed the word SORT in a line
within
my main macro thinking that this would activate the sort macro at that
point, but I'm getting the following error message at the SORT part,
Compile
Error: expected variable or procedure not Module

My full macro is as follows

Public Sub PostToAccounts()

Sort

Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With



strSheetName = Worksheets(ActiveSheet.Name).Range("D4")

Sheets.Add
ActiveSheet.Name = strSheetName & "_Acc"
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Input")

lngPosY = 9 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "DOCUMENT_TYPE"
Range("D1") = "DOCUMENT_YEAR"
Range("E1") = "DOCUMENT_PERIOD"
Range("F1") = "DOCUMENT_DATE"
Range("G1") = "Nominal"
Range("H1") = "Subaccount"
Range("I1") = "Level3"
Range("J1") = "Document_Value"
Range("K1") = "Description"
Range("L1") = "JR_DATE"
Range("M1") = "JR_YEAR"
Range("N1") = "JR_PERIOD"

Range("A2") = "1" ' The document number
Range("B2") = "1"
Range("C2") = "CLAD"
Range("D2") = Worksheets("Input").Range("AC3")
Range("E2") = Worksheets("Input").Range("AC4")
Range("F2") = Worksheets("Input").Range("AC5")
Range("N2") = Worksheets("Input").Range("AC2")
Range("L2") = Worksheets("Input").Range("AC5")
Range("M2") = Worksheets("Input").Range("AC1")

lngOutY = 3

Do While Len(Worksheets("Input").Range("aa" & lngPosY)) 0
Range("A" & lngOutY) = "1" ' The document number
Range("B" & lngOutY) = lngOutY - 1
Range("C" & lngOutY) = "CLAD"
Range("D" & lngOutY) = Worksheets("Input").Range("AC3")
Range("E" & lngOutY) = Worksheets("Input").Range("AC4")
Range("F" & lngOutY) = Worksheets("Input").Range("AC5")
Range("G" & lngOutY) = Worksheets("Input").Range("aa" & lngPosY)
Range("H" & lngOutY) = Worksheets("Input").Range("ab" & lngPosY)
Range("I" & lngOutY) = Worksheets("Input").Range("ac" & lngPosY)
If Worksheets("Input").Range("AF" & lngPosY) 0 Then
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AF" &
lngPosY), 2)
Else
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AG" &
lngPosY) * -1, 2)
End If
Range("K" & lngOutY) = Worksheets("Input").Range("AD" & lngPosY)

lngPosY = lngPosY + 1
lngOutY = lngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
Columns("D:E").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
Selection.NumberFormat = "M/D/YYYY"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:I").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "0.00"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "M/D/YYYY"
Columns("M:N").Select
Selection.NumberFormat = "0"
Cells.Select
Cells.EntireColumn.AutoFit

For i = Range("B700").End(xlUp).Row To 2 Step -1
If Left(Range("B" & i).Value, 3) < "1" Then
If Range("J" & i).Value = 0 Then
Range("J" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=(B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b700").End(xlUp).Row)
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Range("A2").Select

MsgBox "Sheet Converted - Now Post To Accounts"


End Sub




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 863
Default Call up a Macro within another Macro
What is the name of the module containing the Sort macro? Sounds to me as
though it's Sort. As others have mentioned, Sort is a method of the range
object, so it's best to use a different name for your procedures, and it's
also best to not use the same name for both a module and a procedure.

On Fri, 26 Nov 2004 09:17:18 -0000, "John" wrote:

I am trying to run a simple sort macro (this is called SORT) which I have
recorded, within another macro. I have typed the word SORT in a line within
my main macro thinking that this would activate the sort macro at that
point, but I'm getting the following error message at the SORT part, Compile
Error: expected variable or procedure not Module

My full macro is as follows

Public Sub PostToAccounts()

Sort

Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With



strSheetName = Worksheets(ActiveSheet.Name).Range("D4")

Sheets.Add
ActiveSheet.Name = strSheetName & "_Acc"
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Input")

lngPosY = 9 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "DOCUMENT_TYPE"
Range("D1") = "DOCUMENT_YEAR"
Range("E1") = "DOCUMENT_PERIOD"
Range("F1") = "DOCUMENT_DATE"
Range("G1") = "Nominal"
Range("H1") = "Subaccount"
Range("I1") = "Level3"
Range("J1") = "Document_Value"
Range("K1") = "Description"
Range("L1") = "JR_DATE"
Range("M1") = "JR_YEAR"
Range("N1") = "JR_PERIOD"

Range("A2") = "1" ' The document number
Range("B2") = "1"
Range("C2") = "CLAD"
Range("D2") = Worksheets("Input").Range("AC3")
Range("E2") = Worksheets("Input").Range("AC4")
Range("F2") = Worksheets("Input").Range("AC5")
Range("N2") = Worksheets("Input").Range("AC2")
Range("L2") = Worksheets("Input").Range("AC5")
Range("M2") = Worksheets("Input").Range("AC1")

lngOutY = 3

Do While Len(Worksheets("Input").Range("aa" & lngPosY)) 0
Range("A" & lngOutY) = "1" ' The document number
Range("B" & lngOutY) = lngOutY - 1
Range("C" & lngOutY) = "CLAD"
Range("D" & lngOutY) = Worksheets("Input").Range("AC3")
Range("E" & lngOutY) = Worksheets("Input").Range("AC4")
Range("F" & lngOutY) = Worksheets("Input").Range("AC5")
Range("G" & lngOutY) = Worksheets("Input").Range("aa" & lngPosY)
Range("H" & lngOutY) = Worksheets("Input").Range("ab" & lngPosY)
Range("I" & lngOutY) = Worksheets("Input").Range("ac" & lngPosY)
If Worksheets("Input").Range("AF" & lngPosY) 0 Then
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AF" &
lngPosY), 2)
Else
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AG" &
lngPosY) * -1, 2)
End If
Range("K" & lngOutY) = Worksheets("Input").Range("AD" & lngPosY)

lngPosY = lngPosY + 1
lngOutY = lngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
Columns("D:E").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
Selection.NumberFormat = "M/D/YYYY"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:I").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "0.00"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "M/D/YYYY"
Columns("M:N").Select
Selection.NumberFormat = "0"
Cells.Select
Cells.EntireColumn.AutoFit

For i = Range("B700").End(xlUp).Row To 2 Step -1
If Left(Range("B" & i).Value, 3) < "1" Then
If Range("J" & i).Value = 0 Then
Range("J" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=(B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b700").End(xlUp).Row)
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Range("A2").Select

MsgBox "Sheet Converted - Now Post To Accounts"


End Sub


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Call Macro jswalsh33 Excel Discussion (Misc queries) 2 February 21st 10 10:38 AM
How can run a macro ( call a macro) on selection of any filtercriteria? [email protected] Excel Worksheet Functions 7 February 20th 09 12:34 AM
Call an Access macro from an Excel macro Jason W Excel Discussion (Misc queries) 1 May 1st 08 08:33 PM
macro call Steve Excel Discussion (Misc queries) 12 August 25th 06 04:27 AM
How to call a macro in an XLA add-in Peter Laman Excel Discussion (Misc queries) 1 March 10th 05 05:40 PM


All times are GMT +1. The time now is 10:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement