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

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

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

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

Instead of:
kycode="01"
use:
kycode="'01"
--
Gary''s Student - gsnu200787

kycode is a construct from the wbook name so I could add the apostrophe
though it is ironic that prior to this I have spent a lot of effort deleting
unwanted characters from the imported data I am processing <g

But it does work and it's woth going for. Thanks for that. I'm still
curious though as to why the string requires it.

Geoff

"Gary''s Student" wrote:

Instead of:
kycode="01"
use:
kycode="'01"
--
Gary''s Student - gsnu200787

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

Sorry, meant to put tbl.Replace "", ".", xlPart, xlByColumns, False

Geoff

"Geoff" wrote:

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

Fortunately the leading single quote is a PrefixCharacter that does not
appear in the cell. Try this out:

Sub DontQuoteMe()
s1 = "'01"
s2 = "Geoff"
Range("A1").Value = s1
Range("A2").Value = s2
Range("A3").Value = Range("A2").Value & Range("A1").Value
End Sub

and you will see that the single quote does not show up in A3 at all.
--
Gary''s Student - gsnu200787


"Geoff" wrote:

kycode is a construct from the wbook name so I could add the apostrophe
though it is ironic that prior to this I have spent a lot of effort deleting
unwanted characters from the imported data I am processing <g

But it does work and it's woth going for. Thanks for that. I'm still
curious though as to why the string requires it.

Geoff

"Gary''s Student" wrote:

Instead of:
kycode="01"
use:
kycode="'01"
--
Gary''s Student - gsnu200787

Hi Gary"s Student
Interesting enough, though the value in A1 is still prefixed as asiigned.
That's what I have to look out for. More important still is the dbf4 file
created after all my data sorting and cleaning does not import the apostrophe
- that's the prime criteria so I will go with the suggestion.
As I replied to Rick, the importance of Replace is significant to me in
terms of processing speed, please see my response; kycode was only an example
of it's potential use in the project.
Many thanks for the interest and support, the reason why Replace does not do
as expected still concerns but at least there is a fix.

Geoff


"Gary''s Student" wrote:

Fortunately the leading single quote is a PrefixCharacter that does not
appear in the cell. Try this out:

Sub DontQuoteMe()
s1 = "'01"
s2 = "Geoff"
Range("A1").Value = s1
Range("A2").Value = s2
Range("A3").Value = Range("A2").Value & Range("A1").Value
End Sub

and you will see that the single quote does not show up in A3 at all.
--
Gary''s Student - gsnu200787


"Geoff" wrote:

kycode is a construct from the wbook name so I could add the apostrophe
though it is ironic that prior to this I have spent a lot of effort deleting
unwanted characters from the imported data I am processing <g

But it does work and it's woth going for. Thanks for that. I'm still
curious though as to why the string requires it.

Geoff

"Gary''s Student" wrote:

Instead of:
kycode="01"
use:
kycode="'01"
--
Gary''s Student - gsnu200787

Use kycode = "'01 (with the leading apostrophe) in your tbl.Replace
statement...

tbl.Replace "", kycode, xlWhole, xlByColumns, False '''=1

and follow it immediately with this statement...

tbl.Value = tbl.Value

It looks like doing this removes the leading asterisks from all the '01
values.

Rick


"Geoff" wrote in message
...
Hi Gary"s Student
Interesting enough, though the value in A1 is still prefixed as asiigned.
That's what I have to look out for. More important still is the dbf4 file
created after all my data sorting and cleaning does not import the
apostrophe
- that's the prime criteria so I will go with the suggestion.
As I replied to Rick, the importance of Replace is significant to me in
terms of processing speed, please see my response; kycode was only an
example
of it's potential use in the project.
Many thanks for the interest and support, the reason why Replace does not
do
as expected still concerns but at least there is a fix.

Geoff


"Gary''s Student" wrote:

Fortunately the leading single quote is a PrefixCharacter that does not
appear in the cell. Try this out:

Sub DontQuoteMe()
s1 = "'01"
s2 = "Geoff"
Range("A1").Value = s1
Range("A2").Value = s2
Range("A3").Value = Range("A2").Value & Range("A1").Value
End Sub

and you will see that the single quote does not show up in A3 at all.
--
Gary''s Student - gsnu200787


"Geoff" wrote:

kycode is a construct from the wbook name so I could add the apostrophe
though it is ironic that prior to this I have spent a lot of effort
deleting
unwanted characters from the imported data I am processing <g

But it does work and it's woth going for. Thanks for that. I'm still
curious though as to why the string requires it.

Geoff

"Gary''s Student" wrote:

Instead of:
kycode="01"
use:
kycode="'01"
--
Gary''s Student - gsnu200787

Looks like the reason your loop was slow was that you were processing the
range cell by cell.

Try reading the data into a variant array, processing the variant array and
putting the variant array back: something like

dim tbl as variant
dim j as long
dim k as long
dim lCalcSave as long

Application.Screenupdating=false
lcalcsave=application.calculation
application.calculation=xlManual

tbl = range(cells(2, 1), cells(lastrow, 8)

For k=lbound(tbl,2) to ubound(tbl,2)
For j=lbound(tbl,1) to ubound(tbl,1)
If Len(tbl(j,k)) = 0 Then tbl(j,k) = "."
Next j
next k

range(cells(2, 1), cells(lastrow, 8)=tbl


Application.Screenupdating=true
application.calculation=lcalcsave

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Geoff" wrote in message
...
Sorry, meant to put tbl.Replace "", ".", xlPart, xlByColumns, False

Geoff

"Geoff" wrote:

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

Hi Charles
I have only just returned to my post and seen your suggestion. I can report
the following on the table of 6,212 rows and 6 columns I was using to test:

this took 2.58 seconds:
If Not Cells(2, 13) = "" Then
lastcol = 12
Else
lastcol = 13
End If
For Each rng In tbl
If Len(rng) = 0 Then rng = "."
Next

A hybrid of the above and Replace took 0.78 seconds
For i = 7 To lastcol
Set tbl = Range(Cells(2, i), Cells(realLastRow, i))
If Not Cells(Rows.Count, i).End(xlUp).Row = 1 Then
For Each rng In tbl
If Len(rng) = 0 Then rng = "."
Next
Else
tbl.Replace "", ".", xlPart, xlByColumns, False
End If
Next

Your suggestion took 0.42 seconds, the best yet <g

Thanks for that.

Geoff


"Charles Williams" wrote:

Looks like the reason your loop was slow was that you were processing the
range cell by cell.

Try reading the data into a variant array, processing the variant array and
putting the variant array back: something like

dim tbl as variant
dim j as long
dim k as long
dim lCalcSave as long

Application.Screenupdating=false
lcalcsave=application.calculation
application.calculation=xlManual

tbl = range(cells(2, 1), cells(lastrow, 8)

For k=lbound(tbl,2) to ubound(tbl,2)
For j=lbound(tbl,1) to ubound(tbl,1)
If Len(tbl(j,k)) = 0 Then tbl(j,k) = "."
Next j
next k

range(cells(2, 1), cells(lastrow, 8)=tbl


Application.Screenupdating=true
application.calculation=lcalcsave

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Geoff" wrote in message
...
Sorry, meant to put tbl.Replace "", ".", xlPart, xlByColumns, False

Geoff

"Geoff" wrote:

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