Run a macro in active (current cell)

[wtom0412]

Hi all, I hope my question is a simple one. I need to run the following code on many cells in my worksheet.

What I would like to do is click in a cell - run the macro, click in the next cell - run the macro etc etc etc. until all of the cells I need to effect are done.

When I recorded the macro, I hard coded the cell reference in it (F6).

Is there something I can change in the code that will tell it to run in the active cell?

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+u
'
    Selection.TextToColumns Destination:=Range("F6"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True

End Sub

Thank you all as always for your brilliance.

Cheers WT

 

[Michael M]

try

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+u
'
    Selection.TextToColumns Destination:=activecell.value, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True

End Sub

 

[wtom0412]

Thank you Michael,

When I run the macro, I get...

Run-time error '1004':
TextToColumns method of Range class failed

The macro works if I change the cell reference in my original code to the new cell.

Cheers WT

 

[Michael M]

F6 is the destination of the the text to columns.....is that what you want to change ??
Sound like you'd be better of with a loop OR selecting the entire range that you want to Text to Columns !

 

[wtom0412]

Hi Michael,

Yes, I figured it out, it was better to do it by the column.

Thank you so much for your help.

 

[steve the fish]

You just needed activecell rather than activecell.value