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

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets(WksName).Range("A11:F12"), PlotBy:= _
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


A Macro Setting That Would Cause A Run-time Error 1004

May 6, 2009

Is there a setting within excel that could cause a user to get a run-time error 1004? I have a workbook with a macro that adds additional sheets to the workbook when a button is clicked. The thing works fine on my machine and 3 or 4 other machines that I have had guys test it out, but I have a user that it will not work for. He is at a location about 3 hours away so I cant see exactly what he is doing, but he says after he clicks the button he gets a run-time error 1004 that says "Unable to set the PrintQuality property of the Page Setup class". I'm thinking that it is a setting within excel?

View 5 Replies View Related

Error Setting Range In Transcribing Macro

Oct 7, 2009

Trying to write a macro to transcribe data from columns in Sheet1 to Rows in Sheet2. Assuming there is a blank between each record. I get an error on the "Set Rng1....." line.

View 4 Replies View Related

Setting Top Value - Error 1044

Oct 9, 2009

I am using some code to amend the top value of some autoshapes on various sheets.
I am getting a 1004 error: unable to set the top value.

This is a stripped down part of the code, this still gives me exactly the same error

Dim shName As Variant
Dim shTag As Variant
Dim buttName As Variant

Sub GraphButtNew(shName, shTag)
buttName = "B_" & shTag
Sheets(shName).Shapes(buttName & "_1_1").Select
Selection.Top = Range("A52").Top
End Sub

Variables passed to this routine are:
shname = "Sheet1"
shtag = "SH1"

i have an autoshape called "B_SH1_1_1" on Sheet1 but it wont allow me to amend the top value

View 9 Replies View Related

Error Setting Range Variable

Aug 8, 2006

i have compiled a multipage using some borrowed code and some code i have written myself. most of it works, but i have a problem populating listbox2.the error is in Private subCmbFindAllJobNo_Click(). i have put h1 tags around the line of code which shows the error when i debug. this code works ok as a stand alone, so i suspect i have done something wrong in the userform initialise.

Option Explicit
Dim rng As Range
Const FirstRow As Long = 2
Dim r As Long
Dim ans As Variant
Dim MyArray(100, 4)

Public MyData As Range, c, d As Range

Private Sub cmbAmend_Click()

Application. ScreenUpdating = False
Set c = Worksheets("ENTRY SHEET").Range("b4").End(xlUp).Offset(3, 0)
c.Value = Me.DTPicker1.Value
c.Offset(1, 0).Value = Me.TextBox1.Value
c.Offset(2, 0).Value = Me.TextBox2.Value ..........

View 9 Replies View Related

Error When Setting Cell Contents As String In VBA

Aug 20, 2013

The following line of code:

Range("G3").Value = "=("

is giving me the following error:

Application-defined or object defined error

however the following line of code works perfectly:

Range("G3").Value = "(="

I'm basically trying to put various different strings as cell values..

View 5 Replies View Related

Setting Range To Value Fails With 1004 Error

May 9, 2007

I'm trying to find the last row on a sheet and then set the next cell, in column A to a certain value. It fails with Method 'Range' of object '_Worksheet' failed.

'activate the male page

'add to last row of male baggage
lastRow = sMalePicks.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _

'get next row
lastRow = lastRow + 1

sMalePicks.Range(Cells(lastRow, 1), Cells(lastRow, 1)) = Trim(bagNames(x))

View 5 Replies View Related

Run-Time Error Setting Worksheet Variable

Aug 30, 2007

The user is asked for one piece of information "Enter the ID Number.

What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.

Set Wks2 = Worksheets(strWks)

7 rows from the bottom.

My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.

Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.

I get a Time Run Error 9.

It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.

Sub Macro01C_Auto_Resign()
Dim Wks1 As Worksheet, Wks2 As Worksheet
Dim strFind As String, rngFound As Range
Dim lngRow As Long, rngUnion As Range, strWks As String ............................

View 9 Replies View Related

Setting A Data Range For A Chart (error 1004).

Feb 1, 2010

I'm trying to do a simple loop which creates charts based on an ID number. I recorded a macro and has tried to modify it but am having trouble defining the correct reange when settign the data source. Here is my

View 2 Replies View Related

Subscript Out Of Range - Error Relating To Setting Workbook

Jul 11, 2014

I'm trying to do the following

Dim wkb As Workbook
Dim wkb2 As Workbook

Set wkb = ActiveWorkbook
Set wkb2 = Workbooks("F:SuppliersBT Monthly Invoice2014BT Macros.xlsm")

but get a subscript out of range error relating to setting wkb2

The file BT Macros is open - this is where the Macro is stored. What have I done wrong when trying to reference it?

View 5 Replies View Related

Invalid Qualifer Error -setting Variable Using Getpivotdata

Mar 22, 2007

We have a very long macro that at the end sends an email to each training coordinator. Within the body of the email, we want to autopopulate the completion status from a pivot table using getpivotdata.

We first try to set the variable but get an error: [compile error:invalid qualifier].

Dim BDCompletion As String
BDCompletion = Application.PivotTableSelection.GetPivotData(A3, "Business Dev Plan Found")

And this is how we plan to incorporate the variable into the body of the email:

With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = CurrentSheet.Name & " Training Plan Status as of " & Format(Now, "dd-mmm-yy")
.Body = "BD is " & BDCompletion & " complete for 2007 Training Plans as of the date of this email."
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C: est.txt")
.Send 'or use .Display
End With

View 9 Replies View Related

Subcript Out Of Range Error Setting Worksheet Variable

Sep 13, 2006

Subcript Out Of Range Error Coming Now For The Code Which Works For Me Before

Sub WHideRows()

Dim rRange As Range, rCell As Range
Dim strVal As String

Set rRange = Worksheets("WIED PROBLEM WELLS").Range("A11:A110")

For Each rCell In rRange
strVal = rCell(1, 3) & rCell(1, 4) & rCell(1, 5) & rCell(1, 6) & rCell(1, 7) & rCell(1, 9)
rCell.EntireRow.Hidden = strVal = vbNullString
Next rCell

End Sub

i am using the code above to hide the rows which doesn't have any values in all the following Cells 3,4,5,6,7 & 9 or Unhide the rows if there is value in any 1 of the following cells 3,4,5,6,7 & 9 from row number A11 to A110.

The same code works for me before. But now the code is not working. It says below the error message

Run-time error '9':..................

View 9 Replies View Related

Object Required Error Setting Range Variable

Aug 22, 2007

code is getting an error on the bold line below. My error message is run time error 424 - object required.....

View 7 Replies View Related

Excel 2010 :: Setting Range - Runtime Error 1004

May 1, 2012

I am using Excel 2010. Why I do struggle with setting the range below

Set rng = Sheets("Data").Range(Cells(4, firstcol), Cells(lastrow, lastcol))

I get run time error 1004

Tried simple code from msdn and it return same error

Range(Cells(2, 3), Cells(10, 4)).Select

View 2 Replies View Related

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 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

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 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

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

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
End Sub

The series is deleted with:

Sub DeleteMyWife()
On Error Resume Next
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. ->


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

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:= _
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
.ColorIndex = 10
End If
End With
End With
myChart.Chart.ChartType = xlBubble

View 6 Replies View Related

Application-Defined Or Object-Defined Error Setting Validation Message

Nov 10, 2006

I have problems with setting Validation input title and message. When in column "C" I type product code, data validation of corresponding cell in column "D" changes to list of diameters allowed for that product. That works fine. But i also want to set validation message to show allowed diameters.I set it in VBA using named ranges.
But for every second line (13, 15, etc on attached file) I get an application-defined or object-defined error. The rest (14, 16 etc) works ok.

View 4 Replies View Related

Setting Up Deleting Macro

Oct 19, 2013

I'm trying to delete both rows of the same data not just one.






What i need is if there is a same location delete both rows and i cant figure it out to get it to do both of them not just one row.

View 9 Replies View Related

Setting Up An Array On A Macro

Jun 24, 2008

I need to setup an array to look in 408 or so names and I don't want to have to write ("billy", "beth", ... ) etc - I just want it to scan array A3:A408 instead and unsure how to write the macro to do this.

Sub Echo_Monthly_CSR_June()
Windows("Monthly Macro Insert.xls").Activate
Sheets("CSR Data").Select
Dim MyArray As Variant, x As Long, c As Range

MyArray = Array("A3:A408").Select
For x = 0 To 405
With Workbooks("Monthly Macro Insert.xls").Sheets("Copy & Paste Echo")
Set c = .Cells.Find(What:=MyArray(x), After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not c Is Nothing Then..............................

View 9 Replies View Related

Setting Up Password In Macro

Aug 2, 2008

I have set up a form with the following macro with a password. The Macro works fine, but When the user enters the password it appears on the screen. I want the password to be seen as ************ on the screen. Please amend my macro, so that when the user enters the password, it appears as ********** on the screen

Private Sub CommandButton1_Click()
If Me.txtpass.Value "Mike1" Then
MsgBox "Incorrect Password!"
Exit Sub
Me.Label3.Visible = True: Me.Label4.Visible = True
Me.ComboBox1.Visible = True
Me.CommandButton2.Visible = True
Me.ComboBox2.Visible = True
End If
End Sub

View 9 Replies View Related

VBA- Setting Id Of Textbox In Macro Code

Nov 12, 2008

Trying to make a error checking macro (if field is blank, then erro prompt and change textbox backcolour to red). However, I have a lot of text boxes, and my current macro is

View 3 Replies View Related

Trusted Macro Regardless Of Security Setting

Oct 22, 2009

I've been told the latest Excels will allow any workbooks in a certain folder
to run macros as trusted (regardless of security setting?).

Can anyone confirm this please? Is there more to it? What versions are affected? Mine is 2003 so I can't check for this.

View 9 Replies View Related

Page Setting Print Macro

Aug 18, 2006

I have a excel sheet with multiple sheets in it.And I have a VBA macro which split all those sheets into one file each.When I am trying to print one file(SHeet) which has more than 15 columns it is printing in two pages(Width).Is there any code i can use so that when ever i open a file which was split by me and give print command the width should fit to the page and peint.

View 6 Replies View Related

Setting If Function Argument In Macro

Sep 12, 2006

In the code bellow I would like to automate a if function until the 1st emptycell in the row 7 of my spreadsheet.

In my IF function, I would like to refer to a fix cell ("B1") while the other argument is in the same column as the function but two rows bellow.

My problem is that I don't figure out how to refer to a fix cell with the syntax I would like to use t run the macro.

I think it's more clear when you'll see the code

Sub Face2face()
If ActiveCell.FormulaR1C1="=If(R[2]C>=Range("B2"),Range("B2"),R[2]C)"
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))
End Sub

View 8 Replies View Related

Setting Font For A Textbox/Listbox With Macro

Aug 15, 2009

How do you set the font for a textbox and or listbox? I have tried:

View 4 Replies View Related

Auto Run A Macro Even On Medium Security Setting

May 23, 2007

I have my Macro Security setting sets to 'Medium'. How can I auto launch my macro (ex. name - MyMacro) everytime I open the workbook ?

View 9 Replies View Related

Copyrights 2005-15, All rights reserved