Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Sub or function not defined

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Sub or function not defined

    Hi

    I'm trying to compile a code for customised invoicing on excel using macros and get an error. Help on the missing link below

    Regards
    Mfana

    Code:
    Sub nextInvoice()
    Range("L9").Value = "In00" & Text(Now(), "0") & L9.Value + 1
    Range("e22").ClearContents
    Range("g22").ClearContents
    Range("L22").ClearContents
    Range("C25:f36").ClearContents
    End Sub
    
    
    Sub SaveInvWithNewName()
        Dim NewFN As Variant
        'Copy Invoice to a new workbook
        ActiveSheet.Copy
        NewFN = "c:\Documents\Housing\hsInv" & "In00" & Text(Now(), "0") & L9.Value & ".xlsm"
        ActiveWorkbook.SaveAs NewFN, FileFormat: xlOpenXMLWorkbookMacroEnabled
        Print2
        ActiveWorkbook.Close
        NewInvoice
        ActiveWorkbook.Save
    End Sub
    
    
    Sub Print2()
    For I = 1 To 2
    ActiveSheet.PrintOut
    Next I
    End Sub
    
    
    Private Sub Workbook_Open()
    
    
    End Sub
    Last edited by RoryA; Mar 14th, 2018 at 04:51 AM. Reason: Code tags

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,925
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Sub or function not defined

    Welcome to the forum.

    Text is a worksheet function, not VBA. Use Format$ instead.

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub or function not defined

    Thanx Rory. xlOpenXMLWorkbookMacroEnabled, invalid use of property

    Mfana

  4. #4
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sub or function not defined

    FileFormat:= xlOpenXMLWorkbookMacroEnabled
    I visit this site mainly to remember how little I know

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub or function not defined

    Rory

    L9 is a merged cell & the result is object required "423", pls advise, desperate
    Code:
    Sub nextInvoice()
    Range("L9").Value = "In00" & Format$(Now(), "0") & L9.Value + 1
    Range("e22").ClearContents
    Range("g22").ClearContents
    Range("L22").ClearContents
    Range("C25:f36").ClearContents
    End Sub
    
    
    
    
    Sub SaveInvWithNewName()
        Dim NewFN As Variant
        'Copy Invoice to a new workbook
        ActiveSheet.Copy
        NewFN = "c:\Documents\Housing\hsInv" & "In00" & Format$(Now(), "0") & L9.Value & ".xlsm"
        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Print2
        ActiveWorkbook.Close
        NewInvoice
        ActiveWorkbook.Save
    End Sub
    
    
    
    
    Sub Print2()
    For I = 1 To 2
    ActiveSheet.PrintOut
    Next I
    End Sub
    
    
    
    
    Private Sub Workbook_Open()
    
    
    
    
    End Sub
    Last edited by RoryA; Mar 19th, 2018 at 04:40 AM. Reason: Code tags

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,925
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Sub or function not defined

    It should be this:

    Code:
    Range("L9").Value = "In00" & Format$(Now(), "0") & Range("L9").Value + 1

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub or function not defined

    Thanx but how do you handle merged cells

  8. #8
    New Member
    Join Date
    Mar 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sub or function not defined

    Sub or function not defined, it seems 1 is missing a fundamental puzzle
    Code:
    Sub mergeCellsAndCenter()
     
        With Workheet("Invoice")("Merge Cells").Range("e22:f22", "g22:i22").Merge
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Merge
        End With
     
    End Sub
    Last edited by RoryA; Mar 19th, 2018 at 04:40 AM. Reason: Code tags

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,925
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Sub or function not defined

    Please learn to use code tags when posting code.

    It's Worksheets, not Workheet. If the ("Invoice")("Merge Cells") part is an attempt to refer to two sheets, that won't work. You need to process them separately.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •