HOW DO I GET CURRENT ROW & COL INFO?

  • Thread starter Thread starter tootles
  • Start date Start date
T

tootles

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
 
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
 
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
 
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 >
 
Back
Top