need a prompt in a macro

[JMcVeigh]

Ok, absolute beginner here with visual basic...

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 each time I run the macro 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 the RANGE option argument.

Add the necessary logic to trap a response that is NOT a 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]

 

[IknowMe]

How are you determining the range?

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[SkipVought]

And another question...

Why change the range each time?

Do you need the OLD data?

What's the purpose of this requirement?

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]

First question: Do you really need to tell the macro what range to act upon while the code is running?

I ask because, most likely, you can have the macro decide what range to select for you without the user having to answer a question.

Second question: Are you are going to perform the text-to-columns on all rows present in a given column?

[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.

 

[Gavona]

Destination:=selection.offset(0,1) would place the results starting in the column to the right of the selection.


Gavin

 

[Gavona]

With Inputbox how about using
Default = selection.address

Gavin

 

[progyman]

I dont know why people bother with complicated range selections..

named ranges are usable in VBA.

Cheers