Hi guys,
Do you know how i can convert this cell address to a range so that i can access the cells' properties and methods :
Set foundRng = Sheet11.Range("b1:b27").Find(Sheet10.ComboBox1.Text)
Set addCell = Worksheets("Speaking Total").Range(foundRng.Address(RowAbsolute:=False))
foundRng.Address // i attained this address from the "Speaking Total" worksheet after it matched a value in a combobox.
I just want to be able to access the "foundRng.Address" like a normal cell range.
Thanks
Hello marlon1_1,
Every cell on a worksheet is a Range Object. Objects contain the properties, methods, and events in the Object's Class. The Find Method of a Range Object will return either a Range Object if successful or the special system Object Nothing if the search fails.
The code below assigns the result of the Range Find method to the Range Object Variable foundRng. This will either return a Range Object (worksheet cell) or Nothing provided the object variable foundRng has been declared in a Dim statement either as a Range or Variant.
Set foundRng = Sheet11.Range("b1:b27").Find(Sheet10.ComboBox1.Text)
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hi Leith,
A snake would have bit me. Thanks for the proverb and for the answer.
Cheers,
Marlon
Hello Marlon,
You're welcome. I am happy to help.
Is Sheet11 the same as Worksheets("Speaking Total")? There's never a good reason to use aliases like Sheet11, always better to make worksheet references explicit by name or (less frequently) by index number.Originally Posted by marlon1_1
. . .
. . .Set foundRng = Sheet11.Range("b1:b27").Find(Sheet10.ComboBox1.Text) Set addCell = Worksheets("Speaking Total").Range(foundRng.Address(RowAbsolute:=False))
Nope ! Better is to use the worksheet codename rather than its name or its index only if
the worksheet is within the same workbook where is located the procedure …
How much VBA code do you maintain? Where I work, there's a standard which prohibits the use of codenames.
Curios: is there some advantage to codenames? Certainly not clarity. Time performance since no lookup is needed?
One might be that you can use them directly without having to make them the argument to the Sheets object. Of course, the downside is you cannot iterate them in a loop like with sheet names as the argument to the Sheets object.
If you are willing to forego spaces, you can give them a more useful name by renaming a sheet's CodeName via the Properties window.
Last edited by Rick Rothstein; 01-19-2020 at 05:23 AM.
Rick,
codenames should be iterated via the project components, like when you want to access to an external codename …
Re: basic conversion of cell address to a range
The standard of your work likes superfluous maintenance and does very not rule the rest of the world ‼
For an obvious reason : when an user may change the sheet name or the sheets order,
your code does not work anymore and you have to maintain it, mine still works at it is, no maintenance needed !
And as sheet codenames can be amended for clarity by any clever VBA coder …
1st debatable, 2nd granted.
Workbook protection prevents renaming worksheets. Since formulas calling INDIRECT with references to worksheets other than the one containing the formulas would also be adversely affected by arbitrarily renamed worksheets, generally a good idea to protect workbooks. As for worksheet order, if worksheets can't be renamed, how could Worksheets("some_name") be rendered invalid? If worksheet foo originally appeared as the 1st in a workbook, would moving it to the middle or end affect Worksheet("foo")?. . . when an user may change the sheet name or the sheets order, . . .
Granted codenames can be changed, but Sheet11 (Sheet follwed by a positive integer) isn't descriptive.
Last edited by hrlngrv; 01-19-2020 at 10:01 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks