HOW DO I GET CURRENT ROW & COL INFO?
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 |
HOW DO I GET CURRENT ROW & COL INFO?
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 |
HOW DO I GET CURRENT ROW & COL INFO?
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 |
HOW DO I GET CURRENT ROW & COL INFO?
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 . |
HOW DO I GET CURRENT ROW & COL INFO?
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, |
All times are GMT +1. The time now is 11:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com