Setting Range Variable
I would like to set a variable equal to whatever the currently selected range
is on my spreadsheet. I have started my procedure as follows (assuming that a given range of cells has already been selected on my spreadsheet): Dim rng as Range Set rng = Active range of cells currently selected What is the code I need for describing the active range? Thanks. -- Steve C |
Setting Range Variable
"Steve C" skrev i en meddelelse
... I would like to set a variable equal to whatever the currently selected range is on my spreadsheet. I have started my procedure as follows (assuming that a given range of cells has already been selected on my spreadsheet): Dim rng as Range Set rng = Active range of cells currently selected What is the code I need for describing the active range? Thanks. -- Steve C Hi Steve Set rng = Selection MsgBox rng.Address will do the job. -- Best regards Leo Heuser Followup to newsgroup only please. |
Setting Range Variable
Leo,
While your code does output the current range in a message box, I'd like to instead use a For Each statement on that range to identify cells that are bolded, along the lines of this: Dim cell As Range, rng As Range Set rng = Selection For Each cell In rng If cell.Font.Bold = True.... etc. My goal is to identify all the cells in a selected range that are bold and use their addresses to build a formula farther down my spreadsheet. I think what's hanging me up is the syntax of the For Each line. I hope I'm clarifying this better. Thanks for your help. -- Steve C "Leo Heuser" wrote: "Steve C" skrev i en meddelelse ... I would like to set a variable equal to whatever the currently selected range is on my spreadsheet. I have started my procedure as follows (assuming that a given range of cells has already been selected on my spreadsheet): Dim rng as Range Set rng = Active range of cells currently selected What is the code I need for describing the active range? Thanks. -- Steve C Hi Steve Set rng = Selection MsgBox rng.Address will do the job. -- Best regards Leo Heuser Followup to newsgroup only please. |
Setting Range Variable
"Steve C" skrev i en meddelelse
... Leo, While your code does output the current range in a message box, I'd like to instead use a For Each statement on that range to identify cells that are bolded, along the lines of this: Dim cell As Range, rng As Range Set rng = Selection For Each cell In rng If cell.Font.Bold = True.... etc. My goal is to identify all the cells in a selected range that are bold and use their addresses to build a formula farther down my spreadsheet. I think what's hanging me up is the syntax of the For Each line. I hope I'm clarifying this better. Thanks for your help. -- Steve C "Leo Heuser" wrote: "Steve C" skrev i en meddelelse ... I would like to set a variable equal to whatever the currently selected range is on my spreadsheet. I have started my procedure as follows (assuming that a given range of cells has already been selected on my spreadsheet): Dim rng as Range Set rng = Active range of cells currently selected What is the code I need for describing the active range? Thanks. -- Steve C Hi Steve Set rng = Selection MsgBox rng.Address will do the job. -- Best regards Leo Heuser Followup to newsgroup only please. Steve Here's one way: Sub GetAddresses() 'Leo Heuser, 18 Nov. 2006 Dim Cell As Range Dim rng As Range Dim BoldCells As Range Dim BoldCellsAddress As Variant Set rng = Selection For Each Cell In rng.Cells If Cell.Font.Bold = True Then If BoldCells Is Nothing Then Set BoldCells = Cell Else Set BoldCells = Union(BoldCells, Cell) End If End If Next Cell If Not BoldCells Is Nothing Then BoldCellsAddress = BoldCells.Address MsgBox BoldCellsAddress Else MsgBox "No bold cells in selection." End If End Sub Leo Heuser |
Setting Range Variable
I appreciate the code and your time. I'm sure it's going to help!
-- Steve C "Leo Heuser" wrote: "Steve C" skrev i en meddelelse ... Leo, While your code does output the current range in a message box, I'd like to instead use a For Each statement on that range to identify cells that are bolded, along the lines of this: Dim cell As Range, rng As Range Set rng = Selection For Each cell In rng If cell.Font.Bold = True.... etc. My goal is to identify all the cells in a selected range that are bold and use their addresses to build a formula farther down my spreadsheet. I think what's hanging me up is the syntax of the For Each line. I hope I'm clarifying this better. Thanks for your help. -- Steve C "Leo Heuser" wrote: "Steve C" skrev i en meddelelse ... I would like to set a variable equal to whatever the currently selected range is on my spreadsheet. I have started my procedure as follows (assuming that a given range of cells has already been selected on my spreadsheet): Dim rng as Range Set rng = Active range of cells currently selected What is the code I need for describing the active range? Thanks. -- Steve C Hi Steve Set rng = Selection MsgBox rng.Address will do the job. -- Best regards Leo Heuser Followup to newsgroup only please. Steve Here's one way: Sub GetAddresses() 'Leo Heuser, 18 Nov. 2006 Dim Cell As Range Dim rng As Range Dim BoldCells As Range Dim BoldCellsAddress As Variant Set rng = Selection For Each Cell In rng.Cells If Cell.Font.Bold = True Then If BoldCells Is Nothing Then Set BoldCells = Cell Else Set BoldCells = Union(BoldCells, Cell) End If End If Next Cell If Not BoldCells Is Nothing Then BoldCellsAddress = BoldCells.Address MsgBox BoldCellsAddress Else MsgBox "No bold cells in selection." End If End Sub Leo Heuser |
Setting Range Variable
"Steve C" skrev i en meddelelse
... I appreciate the code and your time. I'm sure it's going to help! -- Steve C Thanks for the feedback. Leo Heuser |
All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com