Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell to Text and yesterday's Date?
I'm trying to do a save with a variable filename. I read this might be easier if I set a certain cell to equal the filename that I am looking for and then Dim that as a variable in the code and call on the cell for the name. How do I set a cell to equal something like NS Tues Aug 1st This is what I'm putting in right now that does not work ActiveCell.FormulaR1C1 = "NS " & "=TODAY()-1" I know I am missing a character in there or something, the cell displays as NS =TODAY()-1 I wanted the filename to be something like that, the formatting can change if its easier a different way, but I need "NS" the day of the week, then the month and date. The cell also has to have a "Date-1" or something like that in there since I need it to equal the date from the day before. I want to save this file variably and was going to do something like this Dim filename as string filename = Range("A1") ChDir "D:\My Documents\Temp\" ActiveWorkbook.SaveAs filename:= _ "D:\My Documents\Temp\" & filename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=567405 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell to Text and yesterday's Date?
How About ActiveCell.FormulaR1C1 = "NS " & Date - 1 ?
Charles xl Geek nbaj2k wrote: I'm trying to do a save with a variable filename. I read this might be easier if I set a certain cell to equal the filename that I am looking for and then Dim that as a variable in the code and call on the cell for the name. How do I set a cell to equal something like NS Tues Aug 1st This is what I'm putting in right now that does not work ActiveCell.FormulaR1C1 = "NS " & "=TODAY()-1" I know I am missing a character in there or something, the cell displays as NS =TODAY()-1 I wanted the filename to be something like that, the formatting can change if its easier a different way, but I need "NS" the day of the week, then the month and date. The cell also has to have a "Date-1" or something like that in there since I need it to equal the date from the day before. I want to save this file variably and was going to do something like this Dim filename as string filename = Range("A1") ChDir "D:\My Documents\Temp\" ActiveWorkbook.SaveAs filename:= _ "D:\My Documents\Temp\" & filename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=567405 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell to Text and yesterday's Date?
Nevermind my previous post. I reread your question. Your forgot the =
sign = "=NS " & Today() - 1 Charles xl Geek Die_Another_Day wrote: How About ActiveCell.FormulaR1C1 = "NS " & Date - 1 ? Charles xl Geek nbaj2k wrote: I'm trying to do a save with a variable filename. I read this might be easier if I set a certain cell to equal the filename that I am looking for and then Dim that as a variable in the code and call on the cell for the name. How do I set a cell to equal something like NS Tues Aug 1st This is what I'm putting in right now that does not work ActiveCell.FormulaR1C1 = "NS " & "=TODAY()-1" I know I am missing a character in there or something, the cell displays as NS =TODAY()-1 I wanted the filename to be something like that, the formatting can change if its easier a different way, but I need "NS" the day of the week, then the month and date. The cell also has to have a "Date-1" or something like that in there since I need it to equal the date from the day before. I want to save this file variably and was going to do something like this Dim filename as string filename = Range("A1") ChDir "D:\My Documents\Temp\" ActiveWorkbook.SaveAs filename:= _ "D:\My Documents\Temp\" & filename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=567405 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell to Text and yesterday's Date?
You don't need a cell (and it isn't easier in my opinion)
Dim filename as string, s as String s = "th" if Day(date-1) <= 10 or Day(date-1) 19 then Select Case Right(Day(Date-1),1) * 1 Case 1 s = "st" Case 2 s = "nd" Case 3 s = "rd" Case Else s = "th" end Select End if filename = "NS " & format(Date-1,"ddd mmm d") & s & ".xls" ChDir "D:\My Documents\Temp\" ActiveWorkbook.SaveAs filename:= _ "D:\My Documents\Temp\" & filename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ -- Regards, Tom Ogilvy "nbaj2k" wrote: I'm trying to do a save with a variable filename. I read this might be easier if I set a certain cell to equal the filename that I am looking for and then Dim that as a variable in the code and call on the cell for the name. How do I set a cell to equal something like NS Tues Aug 1st This is what I'm putting in right now that does not work ActiveCell.FormulaR1C1 = "NS " & "=TODAY()-1" I know I am missing a character in there or something, the cell displays as NS =TODAY()-1 I wanted the filename to be something like that, the formatting can change if its easier a different way, but I need "NS" the day of the week, then the month and date. The cell also has to have a "Date-1" or something like that in there since I need it to equal the date from the day before. I want to save this file variably and was going to do something like this Dim filename as string filename = Range("A1") ChDir "D:\My Documents\Temp\" ActiveWorkbook.SaveAs filename:= _ "D:\My Documents\Temp\" & filename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=567405 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display yesterday's date but only for weekdays? | Excel Worksheet Functions | |||
Open File with name incorporating ~yesterday's date | Excel Programming | |||
Open file based on yesterday's date | Excel Programming | |||
Setting text as a background in cell | Excel Worksheet Functions | |||
Setting up a date and time stamp in a cell | Excel Programming |