Inserting prompt in Excel macro

[JMcVeigh]

I recorded a macro and I need to be able to answer a prompt each time I run it.

Here is the macro:

Selection.TextToColumns Destination:=Range("$M$86"), 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)), TrailingMinusNumbers:=True

The Range in the first row $M$86 needs to change and I don't know how to do it.
I would optimally like to be able to click in the cell for the destination range.

Any ideas?
thanks in advance.

 

[SkipVought]

Hi,

Use an INPUT box with a RANGE option (check HELP)

You'll need some contol logic, so that if the users bails out, you won't try to execute the text to columns with no destination range.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[AnotherHiggins]

Please repost this VBA/Macro-specific question in forum707

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.