Clean the value of a cell

[ChrisNor]

Hi,

I have a cell value with " '2005"

I want to clean away the spaces and ' in the cell value.
Is there a Substitute function in VBA?

[mdbct]

One way to do it is to use the Application.Worksheet method.

Please Login or Register  to view this content.

[mdbct]

Sorry - hit Submit instead of Preview

the other method is to use Replace

Please Login or Register  to view this content.

[Richard Buttrey]

Hi

It's not clear whether your cell includes the double quotes or not I'm assuming not and that you just have: space ' 2005

In which case I'm not sure why you need a VBA solution. Why not just use an Excel compound formula? e.g with your example in A1

Please Login or Register  to view this content.

If you need VBA then you could use the REPLACE instruction.

HTH

[ChrisNor]

Hi

It's not clear whether your cell includes the double quotes or not I'm assuming not and that you just have: space ' 2005

In which case I'm not sure why you need a VBA solution. Why not just use an Excel compound formula? e.g with your example in A1

Please Login or Register  to view this content.

If you need VBA then you could use the REPLACE instruction.

HTH

I know this, however I need it as it is part of a larger macro. But thanks for the help.

I also need some help with a for each loop.


Sheets("Historic - Data Feed").Select
Cells.Find(What:="Month", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(-1, 0).Select
Selection = Replace(Selection, "'", "")
Selection = Replace(Selection, " ", "")

I need the above code to loop through all the cells which contains "Month". And do the replace part.

How can I get it to go to the next occurance of Month?

THanks for all the help so far