That is exactly what I have in the first solution. What I'm wondering is why
the Replace function is not replacing a blank cell with a string in a column
formatted as text.

Geoff

"Mike H" wrote:

Geoff,

You dimensioned keycode as a string so enter it as one

Sub liminal()
Dim kycode As String
Dim tbl As Range, rng As Range
Dim finalrecords As Long
kycode = "01"
finalrecords = Cells(Rows.Count, "H").End(xlUp).Row
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

End Sub

"Geoff" wrote:

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