Certainly you can use information from a row in the sheet which contains your titles to determine your offsets ... after all the offsets are simply numbers.
It will definitely not be efficient to have your code look through the titles every time you want to do an offset. You will have to make your code prepare a list the first time it's used which links the title text to the column number. This could be done using an array, but there are probably better structures available to hold the information.
However, it is much easier simply to create a series of statements at the top of your code that define constants which have names relating to the columns' use and contents being the column number or letter. This is quick and easy, and quite simple to maintain if you need to add or remove columns.
The only disadvantage of the latter approach is that it is not dynamic ... i.e. you need to change the constants if you change the rows.
Regards, Dave S
----- Original Message -----Sent: Thursday, June 12, 2014 5:19 PMSubject: [ExcelVBA] Alternative to range.offset which is not robust to added columnsI often use range.offset in lists to make similar calculations in several rowsa sub may be like this (simplified)sub calcrows()for each cell in range("a1.a10")cell.offset(0,4) = cell.offset(0,3) * cell.offset(0,2)next cellend subThis works OK but my problem is when I have more complicated models and more complicated subs its easy to loose "control" if I have to add or move a column, and then have to change all this offset-commands.Can anyone point me to another approach to this sort of operations that can cope with added/moved columns.Is it possible in any way using the titles in first row instead of column numbers in the offset-command?regardsTorstein
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1432 / Virus Database: 3955/7159 - Release Date: 06/11/14
__._,_.___
Posted by: "David Smart" <smartware.consulting@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
.
__,_._,___
Tidak ada komentar:
Posting Komentar