Macros, excel is different from Lotus!

[tomwilson64]

I havent used Macros much since I used Lotus 1-2-3, but I have a need to simplify a repetitive function. I have an Excel spreadsheet with a column of dates in date format, among other columns of text. I am attempting to marry various fields, including the date field, in each row together using the & function. I notice when I try to include the data from the date-formatted cell, it displays in the married string as a numeric value, for example 36077 instead of simply the string text of a date. I can correct how it appears by going to the individual cell, pressing F2 to edit it, pressing the Home key to go to the beginning of the string and and typing an apostrophe to make the cell contents, in this case a numeric date value, into a string that appears as a date. Problem is I have to do this to 2,340 cells.

In Lotus 1-2-3, I would have been able to create a macro as follows:
[edit]
[home]
'
[down]

then name the range, tying it to a ctrl key combination. Then when I went to the first cell in the date column I could execute the macro and just hold down the ctrl key combination until it had converted all the cells.

In excel, I tried the macro recording, but it completely didnt understand what I had done and recorded it as me typing that particular cells' contents into whatever cell I was in so that the same date from when I recorded the macro appeared in every cell I would execute the CTRL key combination on.

How can I create the same in Excel as what I described in Lotus?

[jrnyman]

Try this:

Sub AddApostraphe()
Range("A1").Select 'or wherever you want to start
Do While Not IsEmpty(ActiveCell)
ClValue = ActiveCell.Value
ActiveCell.Value = "'" & ClValue
ActiveCell.Offset(1,0).Select
Loop
End Sub

This will start in A1 and for each cell in column A till it finds an empty one, it will add an apostraphe to the cell value.

[klb]

Or you can change your formula so that the cell that contains the date diplays in text as opposed to the datevalue:

=TEXT(C7,"mm/dd/yyyy")
or
=TEXT(C7,"mmm d, yyyy)

After you edit your formula, you can simply copy the new formula down the rest of the column.