Excel Macro

[Rwac]

Is there a Macro available to move a word to the next cell where there is a comma:

E.g Where || indiciates cells


|Word 1,Word 2,Word 3,|



To



|Word 1|Word 2|Word 3|


Also I am having trouble looping a Macro through all the worksheets in a workbook - does anyone have a macro that they know will work (e.g to apply the above macro across the workbook)?

 

[A[L]C]

Cant you just record doing

Data -> Text to columns?

 

[Rwac]

Cant you just record doing

Data -> Text to columns?

Literally just brainburped and did this with delimiter as commar.

I'm still struggling with looping a macro through a workbook though, not sure why.

 

[A[L]C]

post the macro

 

[Rwac]

post the macro

Posting it helped me think:

Sub filter()
Dim SheetCount As Long

For SheetCount = 1 To Worksheets.Count

Dim Sh As Worksheet
Dim Rng As Range

'make the worksheet active
Worksheets(SheetCount).Activate

With ActiveSheet

Columns("A:A").Select

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
"|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True


End With


Next SheetCount

End Sub


This sorted it. Macro isn't tidied up yet Thanks.

 

[A[L]C]

so its working?

 

[A[L]C]

Check out Smart Indenter and MZtools

http://www.mztools.com/v3/download.aspx
http://www.oaltd.co.uk/Indenter/IndentFrm.htm

 

[Rwac]

so its working?

Hi. Yea it did. Think I just needed to bounce it off someone and everyone was busy.

Cheers for the links I'll have a look.

Macro did work yes, best looping Macro I found.