K
Kyle
I'm normally ok with my excel though I've found a curly
one - I've used Defined Cell names in excel to always set
the correct print area that contains data in a macro,
regardless if more rows or columns have been inserted or
deleted. eg My data range may be from B2
4 so I create
a "Start" cell reference for A1 and an "End" cell
reference for E5. In my macro I then define the goto (F5)
to select both cell references (Start:End) which of
course selects the cell range of data I need. What I'd
like to do now however is have a scrolling period and of
course a scrolling print area. eg I'd like the years 2000
+ 2001 + 2002 broken up into months Mar, Apr, Jun etc and
continues for the 3 consecutive years, I would then like
to enter the starting period of example March and then
for the print area to select from March to March of the
next year. I tried to create a vlookup so that each month
in my spreadsheet would have a period number assigned to
it - eg March00 = 0 April00 = 1 May00 = 2 March01 = 13
etc but I found I could not add the value of the vlookup
result to the goto range. Eg If my period was April00 = 1
I used the formula in the goto of if("A1"<>0,(Start:End),
(Start+A1:End+A1) - in this example I using A1 as the
area where the period is which of course could be defined
name. This didn't work however as the goto function was
trying to use the physical cell reference instead of the
result of the cell - eg (Start:A1) instead of adding the
value to reference (Start:+1) - is there a way that the
goto refence will read the result of the A1 cell or is
there a better way of doing this. Bearing in mind I would
like the print area to move as rows & columns are insert
or deleted. Thanks for any comments you all may have,
regards Kyle
one - I've used Defined Cell names in excel to always set
the correct print area that contains data in a macro,
regardless if more rows or columns have been inserted or
deleted. eg My data range may be from B2
4 so I createa "Start" cell reference for A1 and an "End" cell
reference for E5. In my macro I then define the goto (F5)
to select both cell references (Start:End) which of
course selects the cell range of data I need. What I'd
like to do now however is have a scrolling period and of
course a scrolling print area. eg I'd like the years 2000
+ 2001 + 2002 broken up into months Mar, Apr, Jun etc and
continues for the 3 consecutive years, I would then like
to enter the starting period of example March and then
for the print area to select from March to March of the
next year. I tried to create a vlookup so that each month
in my spreadsheet would have a period number assigned to
it - eg March00 = 0 April00 = 1 May00 = 2 March01 = 13
etc but I found I could not add the value of the vlookup
result to the goto range. Eg If my period was April00 = 1
I used the formula in the goto of if("A1"<>0,(Start:End),
(Start+A1:End+A1) - in this example I using A1 as the
area where the period is which of course could be defined
name. This didn't work however as the goto function was
trying to use the physical cell reference instead of the
result of the cell - eg (Start:A1) instead of adding the
value to reference (Start:+1) - is there a way that the
goto refence will read the result of the A1 cell or is
there a better way of doing this. Bearing in mind I would
like the print area to move as rows & columns are insert
or deleted. Thanks for any comments you all may have,
regards Kyle