Results 1 to 5 of 5

Thread: Formatting with macros
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formatting with macros

    Hello!

    I have a macro that places a formula into a cell. This formula is pulling a value (date and time) from another Excel document. The macro works well, but the format of the cell shows a zero if the reference cell is blank. I am able to change to custom and enter "0:-0;;@" and the zero no longer displays.

    Issue: Now my date is displayed as a 5 digit number. How can I adjust the formula in the cell to change the format?

    I could create an IF statement on the tab code, but I am unsure what to check for. The cell has the formula.

    Any help or possibly just a hint would be great! Thanks!

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,239
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formatting with macros

    Could you post your macro?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    May 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formatting with macros

    Quote Originally Posted by mumps View Post
    Could you post your macro?
    'Dim myMonth As String
    'Dim myDate As Range
    'Dim myName As String
    '
    'Set myDate = Sheets("List of Holidays").Range("A16")
    'myMonth = Format(myDate, "MMMM")
    '
    'myName = "='[Monthly File Receives - " & myMonth & ".xlsm]Monthly Files'!$A$3"
    'Sheets("Monthly File Retrieval").Range("A3") = myName

    So I have the cell function set to pull a value from another document, but the format of the cell needs to be dynamic.

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,239
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formatting with macros

    Try:
    Code:
    Sub amauer()
        Dim myMonth As String
        Dim myDate As Range
        Dim myName As String
        Set myDate = Sheets("List of Holidays").Range("A16")
        myMonth = Format(myDate, "MMMM")
        If Workbooks("Monthly File Receives - " & myMonth & ".xlsm").Sheets("Monthly Files").Range("A3") <> "" Then
            myName = Workbooks("Monthly File Receives - " & myMonth & ".xlsm").Sheets("Monthly Files").Range("A3")
        End If
        Sheets("Monthly File Retrieval").Range("A3") = myName
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    New Member
    Join Date
    May 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formatting with macros

    When I run my macro the cell is blank. The macro is inserting the function. The function when looks to another worksheet and transfers the data. Your macro is looking at the current status of the cell, but I need something that will change the format of the cell after the cell pulls in new data from another worksheet. The reference cell will be blank, but will be updated at some point. I can make everything work, but I get "0" in all the cells. I would prefer the cells be blank until the data is available. The only issue is that the cell formatting puts the date/time as a 5 digit number.

    I run the macro to add the cell formula. Someone updates the reference cell. My sheet gets updated (cell pulls in the date and time).

    My need: The format changes from "Custom" to "Date." Please let me know if you have any questions or if I am not seeing your suggestion correctly.

Some videos you may like

User Tag List

Tags for this Thread

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
  •