Cells(1,1).Text
 

No, there is not worksheet function beyond Text, but you would only have to
enter the format the first time you write the formula, then copy it down.
Doesn't seem that difficult. Also, processing hundreds of cells, even
thousands would take much less than a minute on a modern machine using a for
loop - so not sure what your concern is there either. If you are doing

activecell.offset(1,0).Select
Selection.Value = 3

kind of code, then either remove the select statements and join the code or
use

Application.ScreenUpdating = False ' at the start

Application.ScreenUpdating = True ' at the end

--
Regards,
Tom Ogilvy


"Dave Martin" wrote in message
...
Using Excel 2000.
Is there any way to use a Worksheet function in Excel that mimics the VBA
funtion Cells(1,1).Text?

The =TEXT function requires a format argument. All I want is the contents
of the cell in its currently displayed format. I have spent hours trying
to
concatenate two columns one of which is a date. Excel insists on
converting
the date into its underlying value (eg, 36721.3) when it concatenates with
the contents of another cell.

This is a piece of cake in VBA using the .Text function, but I have
several
hundred cells I need to concatenate, so going through a For/Next loop to
convert all these would be a time consuming step. Any ideas from you
Wizards out there would be greatly appreciated.

Cells(1,1).Text
 

I ran into several issues involving double quotation marks to go in the
=TEXT command. I finally came up with this awkward code. I don't know in
advance which of the two columns have the dates, so I check both. This
works but is roundabout.

Form_A = Temp_Sh.[A1].NumberFormat
Form_B = Temp_Sh.[B1].NumberFormat
Temp_Sh.[H:I].ClearContents
'Convert dates to text strings
If LCase(Form_A) < "general" Then
Temp_Sh.Range("I1:I" & lx).Value = Form_A
With Temp_Sh
With .[H1]
.FormulaR1C1 = "=TEXT(RC1,RC9)"
.Copy Temp_Sh.Range("H2:H" & lx)
End With
.Calculate
.Range("H1:H" & lx).Copy
.[A1].PasteSpecial (xlValues)
End With
End If
Temp_Sh.[H:I].ClearContents
' Same thing for COl B
If LCase(Form_B) < "general" Then
With Temp_Sh
With .[H1]
.FormulaR1C1 = "=TEXT(RC2," & "" & Form_B & "" & ")"
.Copy Temp_Sh.Range("H2:H" & lx)
End With
.Calculate
.Range("H1:H" & lx).Copy
.[B1].PasteSpecial (xlValues)
End With
End If
Temp_Sh.[H:I].ClearContents
'Get Concatenated ID plus ID2, copy the ID's and data into Scatter Data
sheet
With Temp_Sh
With .[H1]
.FormulaR1C1 = "=IF(RC1<RC2,RC1&"" ""&RC2,RC1)"
.Copy Temp_Sh.Range("H2:H" & lx)
End With
.Calculate
.Range("H1:H" & lx).Copy
.[H1].PasteSpecial (xlValues)
End With

"Tom Ogilvy" wrote in message
...
No, there is not worksheet function beyond Text, but you would only have
to
enter the format the first time you write the formula, then copy it down.
Doesn't seem that difficult. Also, processing hundreds of cells, even
thousands would take much less than a minute on a modern machine using a
for
loop - so not sure what your concern is there either. If you are doing

activecell.offset(1,0).Select
Selection.Value = 3

kind of code, then either remove the select statements and join the code
or
use

Application.ScreenUpdating = False ' at the start

Application.ScreenUpdating = True ' at the end

--
Regards,
Tom Ogilvy


"Dave Martin" wrote in message
...
Using Excel 2000.
Is there any way to use a Worksheet function in Excel that mimics the
VBA
funtion Cells(1,1).Text?

The =TEXT function requires a format argument. All I want is the
contents
of the cell in its currently displayed format. I have spent hours
trying
to
concatenate two columns one of which is a date. Excel insists on
converting
the date into its underlying value (eg, 36721.3) when it concatenates
with
the contents of another cell.

This is a piece of cake in VBA using the .Text function, but I have
several
hundred cells I need to concatenate, so going through a For/Next loop to
convert all these would be a time consuming step. Any ideas from you
Wizards out there would be greatly appreciated.