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