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.
Is there a way to check if a named range exists before I run a piece of code? I created a new file that has need for all the old file's ranges plus a couple more, and I want to use the same macro for both. So on the first file I just want to say, if these other named ranges are there, go ahead and do his other thing.
After going though the archives, I could not find how to test for an entry that does not have a sheet to be pasted into. I have a series of worksheets with the month-year for tab labels. The format for these tab labels is ("mmm-yy"). My code will place the new entry into the first available row of the sheet with the same month-year as the entry. I can enter any item with any date in any order and as long as there is a sheet with the same month-year, the entry will be placed into the correct sheet. Temporarily, when I need to add a new month, I click on a CommandButton that uses this code (located in a general module) to make the new sheet:
Is there a way to test (from Excel 2002) whether a specified file (not necessarily an Excel file) exists on my computer? I could specify the complete path.
Got most of this code from the web and I can't get it to work. The part I added was the array and loop bit. As a test I specifically renamed one of the sheets in the file to be something NOT in the array, but it still goes through like it exists (I.e. shexist=True). What did I miss?
Dim wsname As String, shexist As Boolean myarray = Array("Statement of Values", "Vehicle", "Driver Info.", "Revenues by Discipline", "Revenues Geographically", "Employee-Payroll Info. CDN & US", "U.S. Payroll", "Employee-Payroll Info. FOREIGN") For i = 0 To WorksheetFunction.CountA(myarray) - 1 shexist = False On Error Resume Next wsname = myarray(i) shexist = CBool(Len(ActiveWorkbook.Sheets.Item(wsname).Name)) On Error GoTo 0 If shexist = False Then MsgBox "The worksheet '" & wsname & "' does not exist in this file or has been renamed." & _ vbCr & "Please check the file and try again.", vbExclamation, "Consolidate" GoTo THEEND End If Next i
I'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook) When the users insert a new picture he has to set a name for each of them. A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.
But I have a bug if a shape doesn't exist when I try to select it :
Sheets("fiche de controle").Select 'select the sheet with the pictures ActiveSheet.Shapes(Item_old).Select 'select the shapes "Item_Old" Item_Old is a variable corresponding to the picture name.
If the user made a typo, I have a bug. Someone knows how to test if the shapes exist to display a meesage if not ? Or somethig to avoid this kind of bug ?
I'm trying to check if a named range "ActiveCells" exists before deleting it in VBA but can't seem to get the syntax right, I have tried: If Range("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete
AND
If ActiveWorkbook.Names("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete
Can anyone point me in the right direction? I have tried searching but I can only find threads about checking for named objects or about using the toolbars to add/delete named ranges.
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL If B1 = 0 then C1 = NGL If A1 >= 1 and B1 > 0 the C1 = LTG. If A1 >= 1 and B1 < 0 the C1 = LTL. If A1 < 1 and B1 > 0 then C1 = STG If A1 < 1 and B1 < 0 then C1 = STL
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.
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
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..................
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?
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
I have a master sheet and 102 'advisor named sheets'. The master sheet is updated daily with information (number and text) along 1 row in different columns (A:W). I would like when the advisors name is typed (W) =joebloggs! for this whole row to be auto input on joebloggs sheet. There can be multiple of these entered daily for same person, so the information would need to populate on the next row so not to type over the previous entry.
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
if I can use a named criteria as well as a named range. In essence what I am looking to do is count certain cells that meet the criteria in a certain named named range,
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData() Dim counter As Integer counter = Sheets("Imported Data").Range("Counter")
I have a 32 question test that I expect everyone to get most (if not all the answers correct. I have a cell that is counting the number of correct answers.
What I want to do is start a timer when the first correct answer is put in and stop when the last answer is put in. So essentially, I need a way of looking at a cell and starting a timer when it equals one and stopping when it hits 32.
