Solved

Excel VB - format error

Posted on 2016-09-15
12
44 Views
Last Modified: 2016-09-16
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
Comment
Question by:chris pike
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41800097
I don't think that line would produce an error.
What error do you get?
Can you upload the workbook in question?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41800102
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
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 125 total points
ID: 41800110
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
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 125 total points
ID: 41800122
@Rob
NOW is VB Equivalent of worksheet function NOW().
0
 

Author Comment

by:chris pike
ID: 41800134
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
 

Author Comment

by:chris pike
ID: 41800137
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:chris pike
ID: 41800154
@Rob ,
Hi Rob... This is what I get
06.JPG
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41800156
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
 

Author Comment

by:chris pike
ID: 41800183
@JimJam
I get this 07.JPG
0
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 125 total points
ID: 41800202
Uncheck the missing library and add the one according to the version you are using.
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 250 total points
ID: 41800258
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
 

Author Closing Comment

by:chris pike
ID: 41801643
Thanks guys, I have no idea about library's.
Appreciate your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now