Set SeriesCollection With Variables

Aug 16, 2007

I am trying to generate a series of charts. Each row (and 3 columns) is the sole series of each chart, but am having trouble setting the values for the chart seriescollections. I'm using the following

Sub DrawCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long
Dim PointRow As Long
Set Ws = ThisWorkbook.Worksheets("Global Summary")
LastRow = 6
For CurrRow = 5 To LastRow
Set NewWs = ThisWorkbook.Worksheets.Add
NewWs. Name = Ws.Range("A" & CurrRow).Value
Set cht = ThisWorkbook.Charts.Add
With cht
.ChartType = xlBarClustered..................

View 4 Replies


ADVERTISEMENT

VBA SeriesCollection.Values

Jun 22, 2006

Is there anyone out there who've used SeriesCollection.Values before? As I know, there's no way to do double indexing for the values of the point referencing to. So something like the following would not be possible.

CCArray(i) = ActiveChart.SeriesCollection(i).Values

CCArray(i, j) = ActiveChart.SeriesCollection(i).Values(j)

CCArray(I, j) = ActiveChart.SeriesCollection(i).Values

But I need to get the values of the points by some means. Is there anyone who could help me find out the best way for me to put the values into a doubly for...next loop. The following is the code that has the idea of what I'm trying to achieve but , of course, the syntax does not comply to what VB allows.

Redim CC_Array(1 To SeriesCount, 1 To 13) As Integer

For i = 1 To SeriesCount
For j = 1 To 13

CC_Array(i, j) = ActiveChart.SeriesCollection(i).Values(j)
Next j
Next i

View 3 Replies View Related

Toggle Seriescollection In A Chart

Apr 7, 2007

Series are added to a chart on a chartsheet in WB Retirement.xls with the following macro:

Sub AddMyWife()

Application. ScreenUpdating = False
Sheets("Chart").Select

With ActiveChart.SeriesCollection.NewSeries
. Name = "MyWife"
.Values = "='Retirement.xls'!MyWife"
End With

ActiveChart.SeriesCollection("MyWife").Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With

With Selection
.MarkerStyle = xlDiamond
.MarkerSize = 3
.MarkerForegroundColorIndex = 3
.MarkerBackgroundColorIndex = 3
End With

Application.ScreenUpdating = True
ActiveChart.Deselect
End Sub

The series is deleted with:

Sub DeleteMyWife()
Sheets("Chart").Select
On Error Resume Next
ActiveChart.SeriesCollection("MyWife").Delete
ActiveChart.Deselect
End Sub...

View 4 Replies View Related

Test Whether Or Not A Named SeriesCollection Exists?

Apr 20, 2014

How to see if a SeriesCollection that has been named exists so when the corresponding toggle button it clicked it turns on or off the correct corresponding dataseries.

When I create a series I use something like:

[Code] ............

Where the range "tblJim[Quizes]" contains the quiz scores which are the data series pointes and "=Data!$C$6:$C$35" has the respective dates.

When that toggle button is clicked I need to test if the SeriesCollection "Jim" exists so if it does I can delete it and if not, create it.

Also, as much as I hate to multi-subject but usually do anyway, I am wondering if there is good example of doing the same ends by a different strategy - a chart with everyone on it and by applying data filters to the dataset I would turn on and off people's data.

View 2 Replies View Related

SeriesCollection - Delete Specific Entry?

Jun 1, 2006

I am trying to create a chart that will Add or Remove data entries as toggle buttons are clicked. I've fallen at the first hurdle. When I remove one series from the source data, all the SeriesCollection numbers will change down. e.g. ->

ActiveChart.SeriesCollection(2).Delete

where (2) will change to (1) when I delete a series. This then screws up subsequent Removal macros. Is there any way I can either lock the SeriesCollection number?

View 2 Replies View Related

Macro Error Setting SeriesCollection

Nov 7, 2006

I try to plot an XY graph with a VB macro but I don't manage to select the right name of the active sheet.

Sub plot()
WksName = ActiveSheet.Name
Worksheets(WksName).Activate

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets(WksName).Range("A11:F12"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection(1).XValues = "=WksName!R5C8:R643C8"
ActiveChart.SeriesCollection(1).Values = "=WksName!R5C9:R643C9"
ActiveChart.SeriesCollection(1).Name = "=""Specular"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=WksName


End Sub

View 6 Replies View Related

Bubble Graph Code: SeriesCollection.NewSeries

Sep 4, 2006

I'm trying to make a macro that generates a bubble graph from a defined range. I however can't seem to get the SeriesCollection.NewSeries to work.


Dim nr As Integer
Dim myChart As ChartObject
Set myChart = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
myChart.Chart.SetSourceData Source:= Sheets("Sheet1").range("A2:D2"), PlotBy:= _
xlRows
myChart.Activate
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Cells(nr + 1, A)
.XValues = ActiveSheet.Cells(nr + 1, B)
.Values = ActiveSheet.Cells(nr + 1, C)
.BubbleSizes = ActiveSheet.Cells(nr + 1, D)
With .Interior
If Cells(nr + 1, G) >= 50 Then
.ColorIndex = 4
Else
.ColorIndex = 10
End If
End With
End With
myChart.Chart.ChartType = xlBubble

View 6 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Function Back Variables: Function Give Two Or More Output Variables

Jul 27, 2006

Can a Function give two or more output variables. e.g.

Sub a()
x = 5
result = Y(x)
End Sub

Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.

View 2 Replies View Related

Defining Variables Using Variables

Apr 27, 2006

I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...

Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................

View 3 Replies View Related

Erase Variables All The Variables Or To Set The Value Of Them To "0"

Jan 16, 2007

i have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0".
At moment I use following

vHour1_KW1 = 0
vHour1_KW2 = 0
...
vHour1_KW53 = 0

vHour2_KW1 = 0
vHour2_KW2 = 0
...
vHour2_KW53 = 0

until...............................

View 3 Replies View Related

Use 2 IF Variables?

Apr 9, 2014

I'm having a hard time understanding how to accomplish what seems to be a simple result.

I need to display one of two words, based on whether or not a pair of values are above or below the criteria.

FIRST:
IF H6 is greater than 5000
AND
IF AB6 is greater than 25000
Display: Double

SECOND:
IF H6 is less than 5000
AND
IF AB6 is greater than 25000
Display: Single

There is no 3rd scenario, even though logically there should be.

View 13 Replies View Related

Put Variables In URL

Apr 1, 2008

I am trying to put variables in this URL which is related to yahoo finance :

.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"

I defined at the beginning

Dim start_date As Date
Dim end_date As Date
Dim datestring As Variant
start_date = #1/31/2001#
end_date = #11/26/2006#
and put them in datestring

I passed the datestring to a new sub which has the URL:

.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"

So, my question is, i tried to put the (1/31/2001) and (26/11/2007) which is in the above URL which is separated in variables and the URL remain the same

View 11 Replies View Related

COUNTIF With Two Variables

Jul 6, 2012

I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.

What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4

I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")

View 9 Replies View Related

Vlookup With 3 Different Variables?

Jun 26, 2014

vlookup with 3 different variables, for example cells k4 k5 and k6 can be changed to give different variables. Is it possible to have a vlookup function in cell k9 which returns the correct % when the 3 variables are chosen. example, blue boat 48 would return %value of 21%

View 2 Replies View Related

COUNTIF With 2 Variables?

Aug 1, 2014

I am trying to count the status and type of some work so:

Column A would contain the status of the work e.g. open, in progress, closed etc.
Column B would contain the department: ict, development, operations, etc.

I want to do a summary that shows: How many are in ICT are open, closed etc.

I can do a countif to get the total open, in progress etc or total number of ICT jobs but not ICT In progress.

View 1 Replies View Related

Sumproduct - Three Variables

Feb 19, 2008

I am trying to add an additional criteria to the following sumproduct formula. The formula below works fine to add up values that are within a date range. However, I want to add values within a specified date range as well as one additional variable. The additional variable is in column G.

SUMPRODUCT(--($A$3:$A$1015>=$A1026),--($A$3:$A$1015<=$B1026),D$3:D$1015)

View 10 Replies View Related

Use Plus Minus Variables

Nov 30, 2008

Trying to work out the formulas for placing plus minus variables above and below a cell as per worksheet attached. right hand side of the page

View 2 Replies View Related

Distinguishing Between 3 Variables

Jan 20, 2009

I want to make an excel funktion than can distinguish between 3 variables. The three variables are the outcomes of my first function which are 0, 1 and 2 these IF functions can be seen below:
=IF(B11>52;"1";"0")
=IF(B12>5;"1";"0")
Then when I have these results I have tried the following function:
=IF(C11+C12=2;"6-12";"4-6")
By using this function I get an output for 2 (true) and 0 (false), but I would also like an output for 1 (which would be 6 in my work)
It looks like this:
Var 1 58 1
Var 2 4 0
Suggestion 4-6

So in the above case where the sum of var1 and var2 is 1 it is only counted as false for not being 2 and therefore = 4-6 instead of what I would like it to be = 6 (for 1)

View 13 Replies View Related

Countif With Two Variables

Apr 14, 2009

I'm trying to count cells in one column that match a variable only if it also matches a variable in another column. For example, I want to count all of the cells in column A that match "Franklin" only if column D shows "True".

View 5 Replies View Related

Countifs On Two Different Variables

Apr 21, 2009

Say I have a list of part numbers, and each part number has an X or a 0 next to it, depending on my own set parameter.

How do I then report that data on another tab so that it counts how many there are in a set area AND if its an X.

At the moment I have this:

View 7 Replies View Related

Multiple Sum If Variables

Feb 5, 2010

Can I put two variables into a SumIF? forexample I want to sum Column C if Column A is equal to Apples and Column B is equal to Oranges, then sum Column C. Is there a quick formula?

View 3 Replies View Related

Summing With Variables

Feb 18, 2010

I am having real trouble with a formula.

I have used a similar formula for to calculate a column.

Can any one see where I am going wrong. It is the cell highlighted in yellow on the attached spreadsheet.

View 14 Replies View Related

IF Function With Three Variables

Feb 14, 2013

I'm having trouble with my logic again :/

AREA
Tp std
Tp

30
5

So I want the Tp in the 3rd column to show the result:

IF AREA is less than or equal to 20, Tp=Tp std / 2

IF AREA is between 20 and 40, Tp=(((AREA-20)/(40-20))*(Tp std/2))+(Tp std/2)

IF AREA is 40 or greater Tp = Tp std

But as one equation

I have just been struggling with the range part.

View 2 Replies View Related

OFFSET With Variables

May 15, 2009

I am trying to OFFSET from cell A1 based upon a variable in cell A2. The cell I need to OFFSET to is also located in column A, but it could always differ based upon the variable in A2. Here is the piece of code performing this OFFSET.

View 7 Replies View Related

Use Variables In A Range?

Aug 23, 2009

I used a macro to get the following code, but would like to do this with VBA code where I use variables and numbers instead of the
macro's ("I568:J568") notation. Thus I would have something like (lRow, 9) : (lRow, 10) or whatever the correct notation is. Basically I'm trying to copy and paste formulas from one row to the next.

View 4 Replies View Related

Change Variables On The Fly

Sep 3, 2009

Is there a way to change variables while the code is running?

View 11 Replies View Related

Why Declare The Variables

Sep 17, 2009

I know that we should declare all variables at the beginning of a subroutine, in fact I'm told it's good practice to use Option explicit to 'force' variables to be declared, my question is why?

If I don't declare a variable the routine still seems to work OK so what is the downside of not declaring them upfront? Is it just for neatness or common practice or is there another reason?

View 2 Replies View Related

Use Variables In Filenames

Nov 14, 2009

I want to schedule a report to run overnight when I'm not at work, and it needs to save as a filename with the previous day included in it

For example, the filename needs to be saved as
C:Test_variable_ver1.xls where variable is equal to day(today()-1) in excel terms.

View 3 Replies View Related

Count Using 2 Variables

Oct 8, 2008

I have to count a specific column based on two criterias...

Column B Column C

SCD Y
ECD Y
DCD N
SCD N
SCD N

I have tried =COUNT(IF(AND(B3:B51="SCD",C3:C51="Y"),1,0)) But that doesn't seem to give me anything but a "0".

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved