• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

Excel VB - format error

I am getting an error on this line in my VBA

 For Each rngSupervisor In Range("tblSupervisor[Supervisor]")
        sBody = "Here is the expiration report as of " & Format(Now, "yyyy/mm/dd") & " for " & rngSupervisor & "   -  Please Recertify ASAP " & vbNewLine

Open in new window


the error that highlights is the "FORMAT"
Any idea why?  Thanks so much
Chris
0
chris pike
Asked:
chris pike
  • 5
  • 3
  • 3
  • +1
4 Solutions
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I don't think that line would produce an error.
What error do you get?
Can you upload the workbook in question?
0
 
ProfessorJimJamCommented:
can you post the complete code?

there is no problem in the format


if you run that line as seperae procedure like this

sub test
        Sbody = "Here is the expiration report as of " & Format(Now, "yyyy/mm/dd") & " for " & rngSupervisor & "   -  Please Recertify ASAP " & vbNewLine
        MsgBox Sbody
end sub

then you get no problem
0
 
Rob HensonFinance AnalystCommented:
Does VB recognise "Now" for a date? I thought that was only the Worksheet function.

"Date" and "Time" being the VB equivalents of TODAY() and NOW()

Try using this instead:

Format(Date, "yyyy/mm/dd")
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Rob
NOW is VB Equivalent of worksheet function NOW().
0
 
chris pikeAuthor Commented:
Complete Sub
Line 17 is the error
05.JPG
Sub RunExpReport()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2013
    Dim rng As Range
    Dim rngSupervisor As Range
    Dim DaysBefore As Long
    Dim TodayLong As Long
    Dim sBody As String
    Dim OutApp As Object
    Dim OutMail As Object

    TodayLong = Now
    DaysBefore = Range("DaysBeforeExp")


    For Each rngSupervisor In Range("tblSupervisor[Supervisor]")
        sBody = "Here is the expiration report as of " & Format(Now, "yyyy/mm/dd") & " for " & rngSupervisor & "   -  Please Recertify ASAP " & vbNewLine

        'Filter exp date table
        With Worksheets("ExpDate").ListObjects("TableExpDate").Range
            .AutoFilter Field:=3, Criteria1:=rngSupervisor
            .AutoFilter Field:=4, Criteria1:="<=" & TodayLong + DaysBefore
        End With

        Set rng = Range("TableExpDate[#All]").SpecialCells(xlCellTypeVisible)
        If rng.Cells.Count > Range("TableExpDate[#Headers]").Cells.Count Then
            Set OutApp = CreateObject("Outlook.Application")
            sBody = sBody & vbNewLine & RangetoHTML(rng)

            Set OutMail = OutApp.CreateItem(0)

            On Error Resume Next
            With OutMail
                .To = rngSupervisor.Offset(, 1)
                .CC = ""
                .BCC = ""
                .Subject = "Expiration Date Report as of " & Format(Now, "yyyy/mm/dd")
                .HTMLBody = sBody
                .display
            End With
        End If
    Next rngSupervisor

    Worksheets("ExpDate").ListObjects("TableExpDate").Range.AutoFilter
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Open in new window

0
 
chris pikeAuthor Commented:
Roy was helping on this one, HE is away from his desk, need to get this one off my desk ASAP.
Thanks guys
Chris
0
 
chris pikeAuthor Commented:
@Rob ,
Hi Rob... This is what I get
06.JPG
0
 
ProfessorJimJamCommented:
you can replace Format with  this  VBA.Format$

but the best solutuon is to find what library is "MISSING" in the reference
Have you checked for MISSING references under Tools>References?
0
 
chris pikeAuthor Commented:
@JimJam
I get this 07.JPG
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Uncheck the missing library and add the one according to the version you are using.
0
 
ProfessorJimJamCommented:
you need to do the followings

Uncheck "Missing Microsoft Office 16.0 Object Library". then save workbook and then open the reference again and Scroll down to find "Microsoft Office XX.0 Object Library". depending on your version of excel XX goes for version of excel  when you scroll down check the one with highest number.
Click OK. save and error goes
0
 
chris pikeAuthor Commented:
Thanks guys, I have no idea about library's.
Appreciate your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now