Formatting with macros

[amauer]

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!

 

[mumps]

Could you post your macro?

 

[amauer]

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.

 

[mumps]

Try:

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

 

[amauer]

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.