Results 1 to 4 of 4

Thread: apply NumberFormat

  1. #1
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    13

    apply NumberFormat

    Tips Ok:
    Code:
    Cells(Num, 1).NumberFormat = "#,##0_-;-#,##0_-;; @* _- ""23"" "

    Tips Error:
    How to assign the day with a variable

    Code:
    Cells(Num, 1).NumberFormat = "#,##0_-;-#,##0_-;; @* _- "" & Day(DD) & "" "
    I hope this request is possible, thank you in advance

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hello PcMax
    I do not fully understand your question.
    I am not sure if I know what you are asking
    ( I do not have any knowledge of cell formatting )

    But this may be some help

    If you run this code, ( applied to the active worksheet )
    Code:
    Dim DateBit As String
     Let DateBit = Now                   'Typical Format  "26.01.2018 08:48:05"
     Let DateBit = Format(DateBit, "dd") 'Typical Format  "26"
     Range("A10").Value = "          ""26""     "
     Range("A11").Value = "          """ & DateBit & """     "

    This should be the result:
    Using Excel 2007 32 bit
    Row\Col
    A
    10
    xxxxxxxxxx"26"xxxxx
    11
    xxxxxxxxxx"26"xxxxx


    To Explain , If _ VariableWith26InIt = 26 _ then once you are within a string written in VBA, then to get a 26 within that string at any point you may write
    either
    ____26
    or
    ____" & VariableWith26InIt & "


    _ " & _ closes the string and connects you to the VBA varable

    _ & " _ connects to and brings you back into the string

    _ VariableWith26InIt _ is a variable with _ 26 _ in it


    _ Two quotes within a string , like _ "" _ is more difficult to understand, and nobody does as far as I am aware. My guess is that more by accident than design you have some simultaneous going in and out of a string at zero length when a _ “” _ is seen within a string and VBA internally “throws up” a single character __ and / or its syntax requirement of a matching pair is still satisfied to prevent a syntax error by a total string of this form:
    ________ I do not believe anyone fully understands what is going on here

    Alan
    Last edited by DocAElstein; 01-26-2018 at 03:36 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #3
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    13
    Hello

    Thanks for the suggestions, here is an example code.
    The formatting used automatically adapts to the width of the column

    Code:
    Option Explicit
    
    Sub Test()
        Dim K       As Long
        Dim DD      As Long
        For K = 0 To 12
              DD = Date + K
              Cells(K + 2, 1).Value = UCase(Format(DD, "dddd"))
              Cells(K + 2, 1).NumberFormat = "#,##0_-;-#,##0_-;;   @* _- """ & Day(DD) & """    "
        Next K
    End Sub
    Attached Files Attached Files

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi PcMax
    Thanks for the feedback and extra information.
    I think now that I can see what the code does.
    ( I do not understand how that NumberFormat works. But that is just because I have no idea about cell formatting. )

    Ciao!
    Alan
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. [For Loop] Apply Exchange Rate Calculation to Cells
    By nsturk725 in forum Excel Help
    Replies: 1
    Last Post: 05-30-2015, 12:02 AM
  2. Apply filter and delete rows thru Messege Box
    By Prabhu in forum Excel Help
    Replies: 11
    Last Post: 11-04-2014, 07:35 AM

Posting Permissions

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