My macro performs a conversion on each cell in a col. However, diffrent
users may have their worksheet organized differently. For some the column
requiring conversion may be Col #1, and #4 for another. I cannot hard code
the column using range, I need to save to a variable first. How do I do
this?

Thanks!
column challanged

Diffecult to answer without a example but

You can use for example
Columns(ActiveCell.Column)

If you want to use the column from the activecell



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"tootles" wrote in message news:J4%eb.32952$AH4.10948@lakeread06...
My macro performs a conversion on each cell in a col. However, diffrent
users may have their worksheet organized differently. For some the column
requiring conversion may be Col #1, and #4 for another. I cannot hard code
the column using range, I need to save to a variable first. How do I do
this?

Thanks!
column challanged

If you could get your users to select the range first, you could iterate
through the selected range

Sub IterateRange()
Dim myCell As Range
For Each myCell In Selection
'Do your bit here
Next myCell
End Sub

--
HTH
Nick Hodge
Southampton, England

"tootles" wrote in message
news:J4%eb.32952$AH4.10948@lakeread06...
My macro performs a conversion on each cell in a col. However, diffrent
users may have their worksheet organized differently. For some the column
requiring conversion may be Col #1, and #4 for another. I cannot hard
code
the column using range, I need to save to a variable first. How do I do
this?

Thanks!
column challanged

I didn't ask the original question, but your answer just
helped me solve a somewhat similar problem. As to
tootles' original question, I think I have a solution.
You can make a form pop up with a Refedit Control on it,
which allows the user to select a range, and then you
operate on it. Below is a sample of code I have used
successfully. I think I borrowed most of it from MS Help.

UserForm1.Show 'this code goes in the original macro

The following code might go in the click event of a button
on UserForm1 that you create.

For Each c In Range(RefEdit1.Value).Cells
c.Value = "'" & c.Value 'I'm turning a value into
text, but you would insert your own code here

Next

UserForm1.Hide

Hope this helps,

Keith Lorenzen

-----Original Message-----
If you could get your users to select the range first,
you could iterate
through the selected range

Sub IterateRange()
Dim myCell As Range
For Each myCell In Selection
'Do your bit here
Next myCell
End Sub

--
HTH
Nick Hodge
Southampton, England

"tootles" wrote in message
news:J4%eb.32952$AH4.10948@lakeread06...
My macro performs a conversion on each cell in a col.
However, diffrent
users may have their worksheet organized differently.
For some the column
requiring conversion may be Col #1, and #4 for
another. I cannot hard
code
the column using range, I need to save to a variable
first. How do I do
this?

Thanks!
column challanged




.

Keith...

I didnt follow thread BUT

application.InputBox with Type 8 will popup an inputbox with refedit
functionality. Bit simpler then creating a userform :)

Sub UserRangeSelect()
Dim r As Range
On Error Resume Next
Set r = Application.InputBox(Prompt:="Which Range", Type:=8)
If Not r Is Nothing Then MsgBox r.Address
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Keith Lorenzen" wrote:

I didn't ask the original question, but your answer just
helped me solve a somewhat similar problem. As to
tootles' original question, I think I have a solution.
You can make a form pop up with a Refedit Control on it,
which allows the user to select a range, and then you
operate on it. Below is a sample of code I have used
successfully. I think I borrowed most of it from MS Help.

UserForm1.Show 'this code goes in the original macro

The following code might go in the click event of a button
on UserForm1 that you create.

For Each c In Range(RefEdit1.Value).Cells
c.Value = "'" & c.Value 'I'm turning a value into
text, but you would insert your own code here

Next

UserForm1.Hide

Hope this helps,

Keith Lorenzen

-----Original Message-----
If you could get your users to select the range first,