copying a cell for output-only format changes...??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

zoseri - 30 Apr 2004 09:53 GMT

I want to change the format of date data for writing out to a file,
without changing the original data. How can I create a temporary copy
of the current cell to do this? I have tried a couple of things but
they always reference the original cell.

I have a workaround, which is to save the original format, change to new
format, write out date, then change back to saved format. There must be
a cleaner way, but I have already floundered enough. Can someone help
me?

I am new to VBA (obviously...)

Thanks!

Reply to this Message

Dave Hawley - 30 Apr 2004 10:13 GMT

Not too sure I follow, but maybe this code will help?

Dim dDate As Date
If IsDate(Range("A1")) Then
dDate = Format(Range("A1"), "dddd d mmm yyyy")
End If

Reply to this Message

zoseri - 30 Apr 2004 12:25 GMT

Hmmm. I think you are pointing me in the right direction, but I
couldn't quite get your suggestion to work in my code.

I am iterating over the cells in a selection, so my original code looks
like

saveFmt = Selection.Cells(RowCount, ColumnCount).NumberFormat
Selection.Cells(RowCount, ColumnCount).NumberFormat = "yyyymmdd"
Print #FileNum, Selection.Cells(RowCount, _
ColumnCount).Text;
Selection.Cells(RowCount, ColumnCount).NumberFormat = saveFmt

This works but seems goofy. I wanted to find a way to do it that never
changes the original date data.

Thanks again for any help.

Donna

Reply to this Message

Dave Peterson - 30 Apr 2004 23:42 GMT

Maybe something like:

Print #FileNum, format(Selection.Cells(RowCount, ColumnCount).value, _
"yyyymmdd")

And Dave Hawley was suggesting something along these lines (I think):

If IsDate(Selection.Cells(RowCount, ColumnCount).Value) Then
Print #FileNum, Format(Selection.Cells(RowCount, ColumnCount).Value, _
"yyyymmdd")
Else
Print #FileNum, Selection.Cells(RowCount, ColumnCount).Text
End If

> Hmmm. I think you are pointing me in the right direction, but I
> couldn't quite get your suggestion to work in my code.
[quoted text clipped - 18 lines]
> Excel Templates, Training, Add-ins & Business Software Galore!
> Free Excel Forum http://www.ozgrid.com/forum ***

Signature

Dave Peterson
ec35720@msn.com

Reply to this Message