I'm using a simple loop like this to replace certain values in a
range:

For Each i In iRange
If i = "NYCITY" Then i = "NYC"
If i = "FTBENNING" Then i = "FORTBENNING"
'...
Next

The values will always be text, so is it more efficient to use i.Text?
Or maybe i.Value? Or is it best to just leave it as i?

Thanks,
-- Dan

I have no idea what's more efficient, but I would use i.value. I like to
specify the property and not depend on the default.

But if I wanted to be more efficient, I'd add:

Option Compare Text

to the top of the module.

Then
if i.value = "nycity" then i.value = "NYC"
and
if i.value = "nyCity" then i.value = "NYC"
and
if i.value = "NYCITY" then i.value = "NYC"

would all be replaced with NYC.

Second, these are mutually exclusive conditions. A cell's value can't be NYCITY
and FTBENNING.

You could use if/then/else if...

if i.value = "nyCity" then
i.value = "NYC"
elseif i.value = "ftbenning then
i.value = "FTB"
....

Then you're not asking excel to look at each potential string.

In fact, a bunch of those if/then/else if's can get difficult to read. You may
want to look at:

Select case i.value
case is = "NYCITY" : i.value = "NYC"
case is = "FTBENNING" : i.value = "FTB"
case is = ...
End Select

I find this easier to read.

And finally (whew!), I wouldn't do this at all.

I'd just use a series of edit|Replaces against the range. Then instead of
checking each cell for its value, all the cells in the range are changed in one
command.

with irange
.replace what:="NYCITY", replacement:="NYC", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.replace what:=....
end with

If there are lots of these, you could even set up a couple of arrays and loop
through the arrays.

Dim myWhats as variant
dim myWiths as variant
dim ictr as long

mywhats = array("nycity","ftbenning")
mywiths = array("NYC","FTB")

if ubound(mywhats) < ubound(mywiths) then
msgbox "not the same number!"
exit sub
end if

for ictr = lbound(mywhats) to ubound(mywhats)
irange.replace what:=mywhats(ictr), replacement:=mywiths(ictr),
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
next ictr





"Dan R." wrote:

I'm using a simple loop like this to replace certain values in a
range:

For Each i In iRange
If i = "NYCITY" Then i = "NYC"
If i = "FTBENNING" Then i = "FORTBENNING"
'...
Next

The values will always be text, so is it more efficient to use i.Text?
Or maybe i.Value? Or is it best to just leave it as i?

Thanks,
-- Dan

--

Dave Peterson

In this portion:

if i.value = "nycity" then i.value = "NYC"
and
if i.value = "nyCity" then i.value = "NYC"
and
if i.value = "NYCITY" then i.value = "NYC"

would all be replaced with NYC.

I was trying to say that with "option compare text", then nycity, NYCity, NyCiTy
would be picked up by a single statement--not that you'd need to do each
individually.


Dave Peterson wrote:

I have no idea what's more efficient, but I would use i.value. I like to
specify the property and not depend on the default.

But if I wanted to be more efficient, I'd add:

Option Compare Text

to the top of the module.

Then
if i.value = "nycity" then i.value = "NYC"
and
if i.value = "nyCity" then i.value = "NYC"
and
if i.value = "NYCITY" then i.value = "NYC"

would all be replaced with NYC.

Second, these are mutually exclusive conditions. A cell's value can't be NYCITY
and FTBENNING.

You could use if/then/else if...

if i.value = "nyCity" then
i.value = "NYC"
elseif i.value = "ftbenning then
i.value = "FTB"
...

Then you're not asking excel to look at each potential string.

In fact, a bunch of those if/then/else if's can get difficult to read. You may
want to look at:

Select case i.value
case is = "NYCITY" : i.value = "NYC"
case is = "FTBENNING" : i.value = "FTB"
case is = ...
End Select

I find this easier to read.

And finally (whew!), I wouldn't do this at all.

I'd just use a series of edit|Replaces against the range. Then instead of
checking each cell for its value, all the cells in the range are changed in one
command.

with irange
.replace what:="NYCITY", replacement:="NYC", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.replace what:=....
end with

If there are lots of these, you could even set up a couple of arrays and loop
through the arrays.

Dim myWhats as variant
dim myWiths as variant
dim ictr as long

mywhats = array("nycity","ftbenning")
mywiths = array("NYC","FTB")

if ubound(mywhats) < ubound(mywiths) then
msgbox "not the same number!"
exit sub
end if

for ictr = lbound(mywhats) to ubound(mywhats)
irange.replace what:=mywhats(ictr), replacement:=mywiths(ictr),
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
next ictr

"Dan R." wrote:

I'm using a simple loop like this to replace certain values in a
range:

For Each i In iRange
If i = "NYCITY" Then i = "NYC"
If i = "FTBENNING" Then i = "FORTBENNING"
'...
Next

The values will always be text, so is it more efficient to use i.Text?
Or maybe i.Value? Or is it best to just leave it as i?

Thanks,
-- Dan

--

Dave Peterson

--

Dave Peterson

Couldn't have asked for a better response... my code's running about
twice as fast now. Thanks Dave.

-- Dan

I like to specify the property and not depend on the default.

Then you should advocate changing this

For Each i In iRange

to this

For Each i In iRange.Cells

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dave Peterson" wrote in message
...
I have no idea what's more efficient, but I would use i.value. I like to
specify the property and not depend on the default.

But if I wanted to be more efficient, I'd add:

Option Compare Text

to the top of the module.

Then
if i.value = "nycity" then i.value = "NYC"
and
if i.value = "nyCity" then i.value = "NYC"
and
if i.value = "NYCITY" then i.value = "NYC"

would all be replaced with NYC.

Second, these are mutually exclusive conditions. A cell's value can't be
NYCITY
and FTBENNING.

You could use if/then/else if...

if i.value = "nyCity" then
i.value = "NYC"
elseif i.value = "ftbenning then
i.value = "FTB"
...

Then you're not asking excel to look at each potential string.

In fact, a bunch of those if/then/else if's can get difficult to read.
You may
want to look at:

Select case i.value
case is = "NYCITY" : i.value = "NYC"
case is = "FTBENNING" : i.value = "FTB"
case is = ...
End Select

I find this easier to read.

And finally (whew!), I wouldn't do this at all.

I'd just use a series of edit|Replaces against the range. Then instead of
checking each cell for its value, all the cells in the range are changed
in one
command.

with irange
.replace what:="NYCITY", replacement:="NYC", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.replace what:=....
end with

If there are lots of these, you could even set up a couple of arrays and
loop
through the arrays.

Dim myWhats as variant
dim myWiths as variant
dim ictr as long

mywhats = array("nycity","ftbenning")
mywiths = array("NYC","FTB")

if ubound(mywhats) < ubound(mywiths) then
msgbox "not the same number!"
exit sub
end if

for ictr = lbound(mywhats) to ubound(mywhats)
irange.replace what:=mywhats(ictr), replacement:=mywiths(ictr),
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
next ictr





"Dan R." wrote:

I'm using a simple loop like this to replace certain values in a
range:

For Each i In iRange
If i = "NYCITY" Then i = "NYC"
If i = "FTBENNING" Then i = "FORTBENNING"
'...
Next

The values will always be text, so is it more efficient to use i.Text?
Or maybe i.Value? Or is it best to just leave it as i?

Thanks,
-- Dan

--

Dave Peterson

If there are a bunch of things to hunt for, I'd use Select Case:

Select Case i.Value
Case "NYCITY"
i.Value = "NYC"
Case "FTBENNING"
i.Value = "FORTBENNING"
End Select

I might resort to UCase instead of Option Compare Text:

Select Case UCase$(i.Value)
Case "NYCITY"
i.Value = "NYC"

If there are a lot of things, I'd set up a two column range in the worksheet
and use WorksheetFunction.VLookup, or the two-array approach of Dave's.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dave Peterson" wrote in message
...
I have no idea what's more efficient, but I would use i.value. I like to
specify the property and not depend on the default.

But if I wanted to be more efficient, I'd add:

Option Compare Text

to the top of the module.

Then
if i.value = "nycity" then i.value = "NYC"
and
if i.value = "nyCity" then i.value = "NYC"
and
if i.value = "NYCITY" then i.value = "NYC"

would all be replaced with NYC.

Second, these are mutually exclusive conditions. A cell's value can't be
NYCITY
and FTBENNING.

You could use if/then/else if...

if i.value = "nyCity" then
i.value = "NYC"
elseif i.value = "ftbenning then
i.value = "FTB"
...

Then you're not asking excel to look at each potential string.

In fact, a bunch of those if/then/else if's can get difficult to read.
You may
want to look at:

Select case i.value
case is = "NYCITY" : i.value = "NYC"
case is = "FTBENNING" : i.value = "FTB"
case is = ...
End Select

I find this easier to read.

And finally (whew!), I wouldn't do this at all.

I'd just use a series of edit|Replaces against the range. Then instead of
checking each cell for its value, all the cells in the range are changed
in one
command.

with irange
.replace what:="NYCITY", replacement:="NYC", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.replace what:=....
end with

If there are lots of these, you could even set up a couple of arrays and
loop
through the arrays.

Dim myWhats as variant
dim myWiths as variant
dim ictr as long

mywhats = array("nycity","ftbenning")
mywiths = array("NYC","FTB")

if ubound(mywhats) < ubound(mywiths) then
msgbox "not the same number!"
exit sub
end if

for ictr = lbound(mywhats) to ubound(mywhats)
irange.replace what:=mywhats(ictr), replacement:=mywiths(ictr),
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
next ictr





"Dan R." wrote:

I'm using a simple loop like this to replace certain values in a
range:

For Each i In iRange
If i = "NYCITY" Then i = "NYC"
If i = "FTBENNING" Then i = "FORTBENNING"
'...
Next

The values will always be text, so is it more efficient to use i.Text?
Or maybe i.Value? Or is it best to just leave it as i?

Thanks,
-- Dan

--

Dave Peterson

Oops. I missed that one.

I agree with you.

Jon Peltier wrote:

I like to specify the property and not depend on the default.

Then you should advocate changing this

For Each i In iRange

to this

For Each i In iRange.Cells

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"Dave Peterson" wrote in message
...
I have no idea what's more efficient, but I would use i.value. I like to
specify the property and not depend on the default.

But if I wanted to be more efficient, I'd add:

Option Compare Text

to the top of the module.

Then
if i.value = "nycity" then i.value = "NYC"
and
if i.value = "nyCity" then i.value = "NYC"
and
if i.value = "NYCITY" then i.value = "NYC"

would all be replaced with NYC.

Second, these are mutually exclusive conditions. A cell's value can't be
NYCITY
and FTBENNING.

You could use if/then/else if...

if i.value = "nyCity" then
i.value = "NYC"
elseif i.value = "ftbenning then
i.value = "FTB"
...

Then you're not asking excel to look at each potential string.

In fact, a bunch of those if/then/else if's can get difficult to read.
You may
want to look at:

Select case i.value
case is = "NYCITY" : i.value = "NYC"
case is = "FTBENNING" : i.value = "FTB"
case is = ...
End Select

I find this easier to read.

And finally (whew!), I wouldn't do this at all.

I'd just use a series of edit|Replaces against the range. Then instead of
checking each cell for its value, all the cells in the range are changed
in one
command.

with irange
.replace what:="NYCITY", replacement:="NYC", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.replace what:=....
end with

If there are lots of these, you could even set up a couple of arrays and
loop
through the arrays.

Dim myWhats as variant
dim myWiths as variant
dim ictr as long

mywhats = array("nycity","ftbenning")
mywiths = array("NYC","FTB")

if ubound(mywhats) < ubound(mywiths) then
msgbox "not the same number!"
exit sub
end if

for ictr = lbound(mywhats) to ubound(mywhats)
irange.replace what:=mywhats(ictr), replacement:=mywiths(ictr),
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
next ictr





"Dan R." wrote:

I'm using a simple loop like this to replace certain values in a
range:

For Each i In iRange
If i = "NYCITY" Then i = "NYC"
If i = "FTBENNING" Then i = "FORTBENNING"
'...
Next

The values will always be text, so is it more efficient to use i.Text?
Or maybe i.Value? Or is it best to just leave it as i?

Thanks,
-- Dan

--

Dave Peterson

--

Dave Peterson