asked on

Replace function while using EXCELVBA

Hi,
I am trying to use a replace function to replace a field's "," character with spaces. It gives me a compilation error saying Replace function not defined. is there any specific reference that I need to add for using replace functions?. I am using TRIM without any trouble. Can someone suggest any better alternative

Thanks

AzraSound

Try specifying the VBA library as well, e.g.,

VBA.Replace(...)

PaultheGreat

try this out:

ThisWorkbook.Worksheets(1).Range("A1:J10").Replace ",", " ", xlPart, xlByRows

hope that helps!
-PtG

jibinpt

ASKER

This is my code
ReplacedDesc = Replace(lsProcessSheet.Cells(lrow, glDescCol), ",", " ").. VBA.Replace is not working

AzraSound

When you type in VBA and then hit the dot (.) does the list of available function calls come up? Is Replace listed as one of them?

Tommy Kinard

You need to add .Value
ReplacedDesc = Replace(lsProcessSheet.Cells(lrow, glDescCol).Value, ",", " ")

HTH
dragontooth

jibinpt

ASKER

When I type in VBA the dot (.) does come up but Replace is not one among them. RESET is the only one which comes up with RE.

ASKER CERTIFIED SOLUTION

PaultheGreat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AzraSound

Oh, is this VBA 97? Replace didnt come around until VB6, so I guess that would mean VBA 2000?

Tommy Kinard

Are you getting an error on the replace line or is it just not working?

dragontooth

PaultheGreat

thanks for the points and the A! time to go home now... happy programming, people!

Regards,
PtG