Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Variable Ranges, Loop?

  Asked By: Irma    Date: Nov 04    Category: MS Office    Views: 1371
  

I am brand new VBA as well as your group and am hoping you can help
me. I would like my macro to reference a successive cell range each
time it is run. For example I have figures in A1:A5 which represent
Jan-May, and a chart pulling those figures. I always want my chart
to reference the last five months so when I add new figures to the
spreadsheet and run the marco I would like it to pull A2:A6 and so
on each month. I am sure there is a simple way to modify the code.
Any assistance you can offer is much appreciated. I have attached
what I have so far...


Rows("69:69").Select
Selection.Insert Shift:=xlDown
Range("A69").Select
ActiveCell.FormulaR1C1 = _
"=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),""mmm-yy"")"
Range("A69").Select
Selection.Copy
Range("A69").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A69").Select
ActiveSheet.ChartObjects("Chart 9").Activate
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Windows("2008 Oct Month End.xls").Activate
Range("B71").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C-R[-3]C)"
Range("B71").Select
Selection.AutoFill Destination:=Range("B71:O71"),
Type:=xlFillDefault
Range("B71:O71").Select
Range("Q71").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C-R[-3]C)"
Range("P71").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/RC[1])"
Range("P72").Select
ActiveWindow.SmallScroll ToRight:=-10
ActiveSheet.ChartObjects("Chart 9").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(1).Values = "='Payor Mix'!
R65C4:R69C4"
ActiveChart.SeriesCollection(2).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(2).Values = "='Payor Mix'!
R65C5:R69C5"
ActiveChart.SeriesCollection(3).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(3).Values = "='Payor Mix'!
R65C7:R69C7"
ActiveChart.SeriesCollection(4).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(4).Values = "='Payor Mix'!
R65C8:R69C8"
ActiveChart.SeriesCollection(5).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(5).Values = "='Payor Mix'!
R65C9:R69C9"
ActiveChart.SeriesCollection(6).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(6).Values = "='Payor Mix'!
R65C10:R69C10"
ActiveChart.SeriesCollection(7).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(7).Values = "='Payor Mix'!
R65C11:R69C11"
ActiveChart.SeriesCollection(8).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(8).Values = "='Payor Mix'!
R65C12:R69C12"
ActiveChart.SeriesCollection(9).XValues = "='Payor Mix'!
R65C1:R69C1"
ActiveChart.SeriesCollection(9).Values = "='Payor Mix'!
R65C14:R69C14"
Windows("2008 Oct Month End.xls").SmallScroll ToRight:=-4
End Sub

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Betty White     Answered On: Nov 04

If your code works how about reversing the order of the months to
insert a new column A for each new month moving the prior months to
the right? Then the columns A to E will be the last 5 months. Someone
I'm sure can write some code to do what you need but I look for the
simple first.

 
Answer #2    Answered By: Beverly Brooks     Answered On: Nov 04

Thank you for the response. That is a great idea and something I have considered
but am still hoping to wrap my brain around using loops. I am currently working
on automating our month end reporting process. This consists of several
departments each with multiple users pulling different figures and reports from
our database, exporting to excel, cleaning up and updating multiple spreadsheets
in multiple workbooks with charts that pull trended data etc. All of these
workbooks are in the same format (oldest to newest). I am hoping to avoid having
to change the format and graphs on all of the worksheets in all workbooks for
all departments and then convince everyone to get use to the new format... I was
hoping I could work around with the one loop... I appreciate your feedback and
you are so very right! I often tend to make things much more complicated than
they need to be .

 
Answer #3    Answered By: Adelfrid Fischer     Answered On: Nov 04

Looked at your cell descriptions wrong thinking you were adding a new
column when you are adding a new row. So you want the bottom 5 rows.
look in the archives to find the XL go to bottom of sheet then XL up
to find last row. Then set the range by offset up the 5 rows to get
the current cell position. That may work for you. Should be some
examples there or google it. But there are some xperts here that can
write this code in a minute. Or try the PeachExcel list they have lots
of code like this.

 
Answer #4    Answered By: Barabas Cohen     Answered On: Nov 04

Thank you so very much! This is just what I needed to get me going in the right
direction

 
Answer #5    Answered By: Stefan Thompson     Answered On: Nov 04

Are the values in the date field all filled? with no blank rows?
If they are, then you can use a worksheet function to get a count
of the number of rows:

Rowcnt = Application.WorksheetFunction.CountA(Range("A1:A65000"))

then, your last (5) rows are:
Range(Cells(Rowcnt-4,1),Cells(Rowcnt,1))
or
Range(Rowcnt -4 & ":" & Rowcnt)

depending on how you want to use the row numbers.

If the data has some blank cells, then either you have to use a column
that ALWAYS has data to count the rows, or you have to get more creative
(like the previously mentioned Range("A65000").Select and XLup)

 
Didn't find what you were looking for? Find more on Variable Ranges, Loop? Or get search suggestion and latest updates.




Tagged:      

 
 
 
 

Related Post