MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Specifying a range chosen at run time

  Asked By: Anpu    Date: Nov 05    Category: MS Office    Views: 1255

I am trying to create pie chart. My difficulty is in
specifying the region. Can someone please help?

I start with a selected region. I then use an algorithm to only select
those rows that meet a condition. Consequently, I do not know how rows
will be in the second region.

I then use that second region as the underlying data for my pie chart.
I do not know the sheet name "Pie Chart" in this example) ahead of
time and I do not know the range ("A36:B44" in this example), so I
have to make replacements to this code which was generate from record
macro when I created a pie chart.

ActiveChart.SetSourceData Source:=Sheets("Pie Chart").Range("A36:B44"), _

Sub PieCharts101()
Application.ScreenUpdating = False

Const CUTOFF = 0.03

Dim rowCount As Integer

Dim i As Integer 'index to go through the original range
Dim j As Integer 'index to go through the second range
Dim Range1 As Range

rowCount = Selection.Rows.Count
j = 1

'MsgBox "The number of rows is " & rowCount
ActiveCell.Offset(0, 3) = ActiveCell.Offset(0, 0)
ActiveCell.Offset(0, 3).Font.Bold = True
ActiveCell.Offset(0, 4) = ActiveCell.Offset(0, 1)
ActiveCell.Offset(0, 4).Font.Bold = True
ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.EntireColumn.AutoFit

For i = 1 To rowCount
If ActiveCell.Offset(i, 1) > CUTOFF Then
ActiveCell.Offset(j, 3) = ActiveCell.Offset(i, 0)
ActiveCell.Offset(j, 4) = ActiveCell.Offset(i, 1)
ActiveCell.Offset(j, 4).Style = "Percent"
ActiveCell.Offset(j, 4).NumberFormat = "0.0%"
j = j + 1
End If

Next i

ActiveCell.Offset(0, 3).CurrentRegion.Select
ActiveCell.CurrentRegion.Font.Italic = True
Set Range1 = ActiveCell.CurrentRegion
`OK up to here

Now I want the active sheet to be the sheet. I do not know that the
name of the sheet will be "Pie Chart". I want the current region to be
the region that holds the data for the chart. I do not know that it
will be "A36:B44".

The following code came from using the macro recorder and creating a
pie chart. I selected A36:B44 in the "Pie Chart" sheet and then used
the chart wizard to create the chart. The subsequent irrelevant code
is omitted.

ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Pie
Chart").Range("A36:B44"), _
ActiveChart.Location Where:=xlLocationAsObject, Name:="Pie Chart"



1 Answer Found

Answer #1    Answered By: Anuja Shah     Answered On: Nov 05

Try (in Excel97):

SheetName = ActiveSheet.Name


ActiveChart.ChartType = xlPie
ActiveChart.Location Where:=xlLocationAsObject, Name:=SheetName
ActiveWindow.Visible = False

Notice that I didn't use the

ActiveChart.SetSourceData line

This probably works because the data  region is simple and Excel
"defaults" to want you want in that case.

You can skip "Set Range1 = ActiveCell.CurrentRegion" and just use
ActiveCell.CurrentRegion.Select, if you want.

Didn't find what you were looking for? Find more on Specifying a range chosen at run time Or get search suggestion and latest updates.