Hi Rick
I tried all sorts before posting. The reason for pursuing this is one
purely of processing time. Before saving the file as a dbf4 I need to insert
dots where there is no record. The table is always 8 columns but could be
anything up to 62,000 rows.

In tests this construct was taking 3.80 seconds for 8 cols x 6,200 rows
tbl = range(cells(2, 1), cells(lastrow, 8)
For Each rng In tbl
If Len(rng) = 0 Then rng = "."
Next

Whereas using this, takes 0.69 seconds
tbl.Replace "", "*", xlPart, xlByColumns, False

Multiply the difference by say 50 wbooks in a folder and it all adds up.

Geoff

"Rick Rothstein (MVP - VB)" wrote:

I don't think there is a way around this problem. The Replace method you are
using duplicates the Replace item found on Excel's menu bar under the Edit
item. If you leave the "Find what" field blank and put 01 in the "Replace
with" field, the leading zero will be dropped no matter whether you
pre-format the selection to be replaced as text or if you set the "Replace
with" Format to Text. The only way I can get your code to do what you want
is if you set kycode equal to "'01" (there is an apostrophe in front of the
0). By the way, Edit/Replace works correctly if you put '01 in the "Replace
with" field also.

Rick


"Geoff" wrote in message
...
Hi
For speed I am trying to use Replace, on a range object, to insert a
string
but the replacement string is displayed as a single character whereas it
should be 2.



Say kycode = "01" this code displays it correctly as 01

Dim kycode As String
Dim tbl as Range, rng as Range
Dim finalrecords as Long

Range("H:H").NumberFormat = "@"
If Not kycode = "" Then
Set tbl = Range("H2:H" & finalrecords + 1)
For Each rng In tbl
rng = kycode '''=01
Next
End If

This code displays keycode as 1
Range("H:H").NumberFormat = "@"
If Not kycode = "" Then
Set tbl = Range("H2:H" & finalrecords + 1)
tbl.Replace "", kycode, xlWhole, xlByColumns, False '''=1
End If

The benefits of Replace in terms of speed are worthwhile considering the
number of records to search. Any help would be appreciated.

Geoff