EXCEL FEED: Vedr: [ExcelVBA] Alternative to range.offset which is not robust to added columns

Sabtu, 14 Juni 2014

Vedr: [ExcelVBA] Alternative to range.offset which is not robust to added columns

Hi Paul and Dave! Thank you for your advices.

I understand that it may cause my VBA working slowly, but it doesnt matter to much on this project.

For me it's more important with dynamic VBA and I now use:

For Each filnavn In Range("a2.a10")

mottaker = filnavn.Offset(0, Application.Match("mott", Range("a1.p1"), 0) - 1).Value

With titles in row 1 (A1.P1) I now get the right offset value with this match-function.

I will consider your way of doing it in other jobs.

Again thank you for your always quick and useful answers.

Torstein

Den Torsdag, 12. juni 2014 16.15 skrev "Paul Schreiner schreiner_paul@att.net [ExcelVBA]" <ExcelVBA@yahoogroups.com>:

Yup, I'm with David.

I have several applications in which I anticipate some movements of columns.

I have a "Set_Defaults" function in which I read the column headings and either assign column variables

For ColNo = 1 to 30

select case cells(1,colno).value

case "ID"

Col_ID = ColNo

case "Name"

Col_Name = ColNo

...

or I create a Dictionary object:

Dict_Hdr.Add Cells(1,ColNo).Value,ColNo

then, later I utilize either the Variables or the Dictionary object (depending on my approach).

Paul

-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

From: "'David Smart' smartware.consulting@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Thursday, June 12, 2014 6:34 AM
Subject: Re: [ExcelVBA] Alternative to range.offset which is not robust to added columns

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

From: mailto:sejohnse@yahoo.no%20[ExcelVBA]

To: ExcelVBA@yahoogroups.com

Sent: Thursday, June 12, 2014 5:19 PM

Subject: [ExcelVBA] Alternative to range.offset which is not robust to added columns

I often use range.offset in lists to make similar calculations in several rows

a 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 cell

end sub

This 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?

regards

Torstein

No virus found in this message.
Checked by AVG - http://www.avg.com/
Version: 10.0.1432 / Virus Database: 3955/7159 - Release Date: 06/11/14

__._,_.___

Posted by: Torstein Johnsen <sejohnse@yahoo.no>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

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

----------------------------------

Visit Your Group