Variable Ranges in Macros

  • Thread starter Thread starter ayqwasi
  • Start date Start date
A

ayqwasi

At the end of every month I import a financial report and format it for
populating 30 to different subsidiary financial reports. I have created
a macro that does this formating but, each month, the length of the
imported file changes causing the old static macro to be unusable. I
would like to create a macro that can accomodate this variability. Any
ideas?

Note: The original report is about 3,400 rows long and contains
financial data for over 30 subsidiaries. The macro removes headers
marking page breaks for same subsidiary but leaves the starting header
for new subsidiary. I have all 30+ worksheets in one workbook. Each
worksheet has a standard financial format which is populated from the
import file using "VLOOKUP" statements. Thus each month the financial
results are updated by simply pasting the new import over the old one.
The problem comes when the new import is longer than the original in
the report.
 
If you can pick a column that is always populated, you can use that to determine
your range:

dim myRng as Range
with activesheet
set myrng = .range("a1:E" & .cells(.rows.count,"A").end(xlup).row)
end with

I used column A to to find the last row and had my range extend from column A to
column E.

And if you write your =vlookup() formulas like this:

=vlookup(a1,sheet2!$a$1:$e$9999,2,false)

Try using the whole column:
=vlookup(a1,sheet2!$a:$e,2,false)

As long as you know that there's nothing at the bottom of sheet2 that will
interfere with the =vlookup(), then you should be ok.
 
You could try using a dynamic named rangeusing the offset function. This
will be ok provided that you use a column for the height of the range that
you know will have data in it regardless. That way you know that the height
of the range will always include all rows.

The offset command uses the following syntax:
OFFSET(reference,rows,cols,height,width)

Reference is the starting point - make this a fixed reference

Rows is the number of rows up or down you wish to move

Columns is the number of columns left or right you wish to move

height and width are optional and can be determined by using the count or
counta functions

If you insert counta in the row height space (eg assuming the start of data
is cell A1 the formula might read
=OFFSET(sheet1!$A$1,,,COUNTA(sheet1!$A$:$A$),30) and the number of columns
wide for the data range as the width then no matter how large or small your
data set the named range will always refer to the correct number of rows.
You then merely refer to the named range in your macro. If you are using a
new spreadsheet each time and merely appplying a macro from your
personal.xls collection you can put some code in your macro to create the
named range.

For a more detailed explanation of dynamic named ranges go to
http://www.beyondtechnology.com/geeks007.shtml

I hope this helps you - I use it all the time.

Regards,

Jeremy
(e-mail address removed) "making use of the tools at our desktop"
 
Back
Top