+ Reply to Thread
Results 1 to 2 of 2

Percentage not carrying over.

Hybrid View

  1. #1
    Registered User
    Join Date
    03-07-2018
    Location
    Northampton, England
    MS-Off Ver
    MS Office 2016
    Posts
    7

    Percentage not carrying over.

    With the section & Range("Q2").Value & needs to pull a percentage from the sheet in question however when ever it pulls though to email it comes as a decimal number and not a percentage.

    Could anyone please help me solve this. thanks.

    Sub Send_On_Road_Data()
    '
        Sheets("On Road Performance").Select
    '
        With Sheets("On Road Performance")
            If .Range("B3") = "" Then
                MsgBox "Please Insert On Road Performance Data"
                Exit Sub
            End If
        End With
    '
        Sheets("On Road Performance").Select
        Range("B3").Select
    '
    Dim strto As String, strsub As String, strbody As String, Fnt As String
    Dim rng As Range
    Set rng = Sheets("On Road Performance").Range("OnRoadPerformance[#All]").SpecialCells(xlCellTypeVisible)
    strto = " "
    strcc = ""
    strsub = "On Road Performance - " & Date - 1
    strbody = "Morning All," & "<br>" & _
            "" & "<br>" & _
            "Please find bellow the On Road Performance for yesterday. Your average FDDS was " & Range("Q2").Value & "." & "<br>" & _
            ""
                
    Fnt = "<p style='font-family:Calibri;font-size:11.0pt'>"
    With CreateObject("Outlook.Application").Createitem(0)
        .Display
        .To = strto
        .Cc = strcc
        .Subject = strsub
        .HTMLBody = Fnt & strbody & RangetoHTML(rng) & .HTMLBody
        '.Send
    End With
    '
        Range("B2").Select
    '
    Dim wb As Workbook
    Set WorkRng = Range("A1:P400")
    WorkRng.Copy
    Set wb = Workbooks.Add
    With wb
        ActiveSheet.Paste
        .SaveAs "Test File" & "\" & "On Road Performance - " & Range("C3").Value & " - " & Format(Date, "dd.mm.yyyy") & ".xlsx"
        .Close
    End With
    '
    MsgBox "Congratulations, Data Saved!"
    '
    End Sub
    
    Function RangetoHTML(rng As Range)
    Dim fso As Object, ts As Object
    Dim TempFile As String, TempWB As Workbook
    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
    With TempWB.PublishObjects.Add( _
        SourceType:=xlSourceRange, _
        Filename:=TempFile, _
        Sheet:=TempWB.Sheets(1).Name, _
        Source:=TempWB.Sheets(1).UsedRange.Address, _
        HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                            "align=left x:publishsource=")
    TempWB.Close savechanges:=False
    Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    End Function

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Percentage not carrying over.

    Formatting is as the word says a format, when you use the value it's the actual value in that cell that is used, so if you see 100% the value is 0.1 (or 0,1 depending on the used decimal separator)
    So when you want to pass values it's the value that is shown and not the formatting so you must include the formatting too.
    You id that with the date value also do it with the format something like shown below,

     Format(Range("C3").Value ,"#0.0 %")
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Carrying Formatting Over
    By mwierenga in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2012, 05:00 PM
  2. carrying forward
    By pilou in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-30-2009, 02:45 PM
  3. carrying info through months
    By carlalwalker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-06-2008, 12:14 AM
  4. Carrying columns
    By slickmouse in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-12-2007, 08:58 AM
  5. Carrying on a Sequence?
    By cjconnor24 in forum Excel General
    Replies: 2
    Last Post: 08-13-2007, 12:04 PM
  6. Carrying Over Corresponding Data
    By zacharyz in forum Excel General
    Replies: 5
    Last Post: 12-21-2006, 12:38 PM
  7. [SOLVED] Carrying formatting automatically to new W/S
    By Jonah in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  8. Carrying formatting automatically to new W/S
    By Jonah in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1