CODEWITHSUNDEEP
excelvbareplaceevaluate
user1721447
user1721447

Reputation: 51

EXCEL VBA EVALUATE and REPLACE

I need to make Evaluate work for the Replace function. The code is supposed to replace " -0700" with nothing. The code is provided below along with data in column C:

Set sh = ThisWorkbook.Sheets(1)
Set rangc = sh.Range("c2:c10")
For Each area In rangc.Areas
  area.Value = Evaluate("IF(ROW(" & area.Address & ")," & Replace(area.Address, " -0700", "") & ")")
Next area

-This is the data in the sheet:

ColumnC(original data)
 -0700
sdfg -0700
how  -0700 saf
2016-10-16 10:13:41 -0700

ColumnC(After code is run)
-700
sdfg -0700
how  -0700 saf
10/16/2016 10:13

Why does the code did something to the first row (-700) but did not replace with "". Also it did remove the " -700" in 4th row (10/16/2016 10:13). But it did not work for 2nd and 3rd rows? How do I make it work for strings or numbers?

I am trying to use similar code to replace partial string in another string very quickly. I cannot accept using looping method because there are hundred thousands of rows.

Upvotes: 1

Views: 1045

Answers (2)

Black cat
Black cat

Reputation: 6271

There are some errors in the code

Set Sh = ThisWorkbook.Sheets(1)
Set rangc = Sh.Range("c2:c10")
For Each area In rangc.Cells 'iterate through the cells
  area.value = Evaluate("IF(ROW(" & area.Address & "),""" & Replace(area, " -0700", "") & """)")
Next area

In the Replace method refer to the area object not the string address of it. When it creates the text without the " -0700" the generated text should be enclosed in parentheses for the IF function also.

Upvotes: 0

arcadeprecinct
arcadeprecinct

Reputation: 3777

You don't need to loop through the whole range. You can just use .Replace method which is pretty fast:

Set sh = ThisWorkbook.Sheets(1)
Set rangc = sh.Range("c2:c10")
rangc.Replace What:=" -0700", Replacement:=""

Upvotes: 0

Related Questions