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

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

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

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