Problem w/Chart Axis labeling with VBA

I have a worksheet with 26 columns and 95 rows.  I select and highlight columns
"v" and "z"  Column "v" has the label BE and column "z" has the label PP.

I have a vba macro shown below that plots the xyscatterchart and labels the x
and y axis.  the xyscatterchart always automatically chooses the z column (the
furthest righthand column) as the y data points and the v column as the x data
points.  This data point selection happens no matter which column I select
first.(I select a column and then hold down the <ctrl> and select a 2nd column).

However, when I find the axis labels from the rng=selection, the rng(1).value is
always the label of the column I select first.  If I select column z first and
then column v, the x axis label rgn(1).value =PP while the xyscatterchart makes
the z column values as the y axis values.

Since the selection of the 2 columns for the xyscatterchart is random, and the y
axis will always be the furthest righthand column chosen how do I find the
correct column labels without making sure I select the left most column first?

Also another weird thing is that when I select any 2 columns in the worksheet,
lNumRows=65536 and lNumCols=1.  lNumRows should be 95 and lNumcol should be 2.
Why is this happening?

Thanks for any help with this problem.


==============vba code========================

Sub myScatterChart()

'On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Dim curwk As Worksheet
    Dim SS As String
    Dim myCell As Range
    Dim rng As Range
    Dim myName As String, sColLabel1 As String, sColLabel2 As String
    Dim ChartName As String
    Dim ii As Long
    Dim lNumRows As Long
    Dim lNumCols As Long
    
    ii = 0
    myName = ""

    SS = ActiveSheet.Name
    Set rng = Selection
    With rng
      lNumRows = .Rows.Count
      lNumCols = .Columns.Count
      MsgBox "rngrows=" & lNumRows & " rngcol=" & lNumCols & _
             " rng(1)=" & rng(1).VALUE
    End With

    For Each myCell In rng
      If Not IsNumeric(myCell.Value) Then
        ii = ii + 1
        myName = myName & myCell.Value
        'MsgBox "ii=" & ii & " " & myCell.Value
        If ii = 1 Then sColLabel1 = myCell.Value
        If ii = 2 Then sColLabel2 = myCell.Value
      End If
    Next myCell

    Charts.Add
    
    ChartName = ActiveChart.Name
    If myName <> "" And Not myNameExists(myName) Then ActiveChart.Name = myName

    With ActiveChart
      .ChartType = xlXYScatter
      .SetSourceData Source:=rng, PlotBy:=xlColumns
      .Location Where:=xlLocationAsNewSheet

      .Move After:=Sheets(Sheets.Count)
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = sColLabel2
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = sColLabel1
      .PlotArea.Select
      With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
      End With
      With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
      End With
      With .Axes(xlCategory)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
      End With
      With .Axes(xlValue)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
      End With
      
      .SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward _
        :=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select
     .PlotArea.Select
   End With
   
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
   
End Sub
0
Dennis8944 (79)
7/19/2005 8:59:55 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
517 Views

Similar Articles

[PageSpeed] 50

Reply:

Similar Artilces:

Sub-categories on the X axis
I am trying to graph data from various schools and want to display data from 2002-03, 2003-04, 2004-05 and so on with the school name under that range of dates. I only want the school name to appear once. The data adjacent to those dates had the school name repeated for each year but when I merge the repeated school names into one field the chart now show only one school name but it is directly under the first data point. I want it to be centered under the dates associated with that particular school. Thanks for your help. What version of Excel? In 97 the behavior was as you describe...

Having problem of Outlook not receiving new e-mails
I have been using Outlook for over 1 year with Comcast Broadband. Suddenly last week, I received a message that Outlook 2000 was experiencing a problem when receiving new e-mails and was going to shut down. I re-opened Outlook and it looked different and then it asked for me set up a profile and I canceled it. But after that window coming up a couple of times after it shut down, I decided to put in a new name and then I could not get to my old e-mails and it acted like a new set up. Then I really got frustrated. I was eventaully able to get back to my old e-mails and have been able t...

Naming charts on own sheet
Hi. I have a series of charts (which are all contained on their own sheets). I need to name each of the charts (as they will be used by someone else in a macro). I have tried clicking on them and also pressing shift before clicking on them, and I am not able to change the name in the name combo box. Can anyone advise me of how I can change the names. Thanks for your help. Hi, If you have chart sheets you can change the name by simply changing the sheet tab name. What you described is the method used on chartobjects, which are usually on a worksheet. Cheers Andy -- Andy Pope, Mi...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

VLookup in VBA giving error message
Hi, I'm having trouble making the vlookup function work in VBA. the values that are used are all dates, I don't know if that makes a difference. Worksheets("sheet1").range("a1").value = application.WorksheetFunction. _ Vlookup(Range("c1"), range("a:a"), 2) this gives me a run-time error '1004' Unable to get the vlookup property of the Worksheet function class so i tried this: Worksheets("sheet1").range("a1").value = application.Vlookup(Range("c1"), _ range("a:a"), 2) but this gives me a #REF ...

An Outlook 2003 Problem
I just upgraded from Outlook 97 to Outlook 2003. However, when trying to reply, forward, and compose with Outlook 2003, I got this error message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." The problem persisted. I restarted Outlook 2003, but it didn't help. Any suggestions? Thanks. How did you upgrade Outlook 2003 from Outlook 97? Anyway If you use Hotmail or MSN mail, check MSKB info below. You receive a "The messaging interface has returned an unknown error " error message when you try to use Outlook to...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

loan amortisatio chart not updating
Hi, I use MS Monet 2007 premium. I have created a loan amortisation account which breaks up my monthly instslment into pricipal and interest. The loan commencd from 7 October 2005 and is for a period of 5 years. The problem is that the loan account does ot show any loan instalments beyond 7 October 2006 (exactly 1 year after the commencement). Why is this happening. Why is the account not updating with instalments which have been debited to my account after 7 October 2007. Please help. Were you depending on downloaded transaction data for this account? Did the download link br...

How do I make a chart with several times during a day
Hi, This seems like it may be a simple thing, yet I can not for the life of me figure out how to do this (or at least semi-easily with VBing it for a while). I have a simple table filled with the following information (example for simplicity): 12pm 5pm 10pm 11/20 5 4 7 11/21 5 4 7 11/23 5 4 7 11/24 5 4 7 11/25 5 4 7 So basically I am keeping track of a numeric value three times a day. I would like to make a chart of it with...

Exchange Server 2000 Secuirty Setup problems
Dear I have apply wrong setting to our exchange server 2000 . The wrong setting as from exchange manager . mail store , I have apply everyone send as and receive as access right, the group included remote access via offline sync. This setting once apply , all user access other mailbox without apply access right. I have try disable this setting, after all local office user no problems, but a remote offline sync laptop user does not work . The error as below: Unable to display the folder. Microsoft Outlook could not access the specified folder location. The file C:\exchange\shawnb.ost cannot b...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

Adding a certain text label in a excel chart
I am plotting in regularly basis a certain set of data in excel. Based on some data analysis this set of data has to be fitted to these equations: y = 1/x^a (1) and /or y = b/x^c (2) from data analysis, constants a, b and c are found and are placed lets say in cells A1, B1, C1. On my graph, I am putting then two small text labels where the real equation is displayed: smth. like: y = 1/ x^3.45 and / or y = 0.256 / x^3.12 The whole process is similar with excel curve fitting, when the “show equation on chart” is checked. Thank you in advance My question is: Can ...

Label Error with image?
Hi, We are trying to print a label for jewelry, the only issue we seem to have is that when an image is put into the label designer the image is the right dimensions but instead of being an image it is a box with red lines cris-crossing with the word 'ERROR' displayed in the center. This all happens before printing, although when we try to print there is no image. So what is it that we are doing wrong to not have an image displayed in the Label designer of RMS PO Manager. Any help[ would be greatly appreciated! Thanks ___________________ Dave Dave, In Label Designer, you have ...

survival chart
Hi! Does anybody have an idea how it would be possible to trick Excel into making a survival plot? That is a stepped graph ploting cumulative survival on the y axis and time slots on the x. Any suggestion would be most appreciated. Andrej Andrej - I don't have a 'survival plot' per se, but you could look for ideas on my web site. The cumulative probability plot sounds like a possible candidate: http://www.geocities.com/jonpeltier/Excel/Charts/ProbabilityChart.html If it doesn't give you any idea, post back with some typical data, and we'll have a look. - Jo...

Chart "Benchmark" Line Graph Question
I am generating a graph of many team's monthly audit productivity percentages (bar graph) with a "benchmark" (line graph) at 95% (y axis) across 6 month period (x axis). The problem is that the line graph appears on the graph but the ends of the line are centered in the end columns (left & right). Is there as simple way to extend the benchmark line out to the edges of the graph? MJ Have a great day and live life with passion! ...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

Chart printing issue in Excel 2007
A spreadsheet with charts was created using Excel 2003. I have Excel 2007 and saved it in compatibility mode. I inserted a couple colored lines on the chart and created my own legend based on these. A couple of issues: 1. When I close the file or even minimize, 2 of the colored lines on a couple of my legends disappear upon reopening. 2. When I try to print a chart, it looks good in Print Preview, but then looks magnified,half off the page, and only one of my drawn lines is printed. When someone with 2003 prints, the sizing is correct, but all of the colored drawn lines are missing...

trying to link maps and pie charts
Hello I was trying to link sales data stored in an excel table with a specific country in MapPoint. This software allows you to do this very easily. You can even picture the data in piecharts (% sales for each competitor). However there is very little flexibility with the look of the result: you cannot choose the color of the slices of the pie, you may not display data on the graph, and the maps do not look very professional. I know it is possible to link automatically a table with a shape in visio. Can this shape be a given country/region on a map? Can the callouts have th...

Excel Problem #3
I have win2000 with Office 2000 in the network! When i try to rename one excel file my computer make restart. This hapening not to all files,and all files there are in the same folder in the network! What hapen,and what i can do so as to work out the problem? I have all the required permissions on that network folder. No one else has any of the files open. They are not shared workbooks. ...

Interesting Solver problem (Solver encounters an error)
Here's an interesting problem, I wonder if anyone has any thoughts o this. Recognize that my real problem is very complex (severa intermediate calculation including some iterative steps), but th problem I'm having seems similar (conceptually anyway) to this simpl problem. Given a data set: x,y 10,3.9 8,3.2 7,2.8 6,2.2 5,1.4 4.5,0.8 4,0.01 3.8,-0.4 3.6,-1 3.5,-1.4 3.4,-1.8 3.3,-2.4 3.2,-3.2 3.1,-4.6 3.05,-6 One could look at the data and say, "that looks like the curve y=ln(x) but with a different asymptote other than the y-axis and possibly scaling factor." So we choos...

Problems with MFC list controls
I have a large list control with many inventory control items displayed within. When someone single clicks on an item, it displays information about that item in another part of the dialog. I have a three-fold problem, the master list has multiple columns in it, in order to make the selection work properly, only column 0 can be selected, is there a way to make a valid row selection if you select an item using any of the other columns? Second issue is the highlight of the selection. Only column 0 in the list control will highlight, is there a way to make the entire row highlight when an...

Next button problems
I have a next button in a form that I have criteria for. I have a date field that has to be entered before going to another record. When I click the next button I get the pop up that says click update to continue but the form displays the next record instead of staying on the current record that needs updating. Here is the code that I am using. ____________________________________________________________________________________ Private Sub Command39_Click() If IsNull([Post Called Customer]) Then MsgBox "You must click the UPDATE button to continue!!!" Cancel = True End If On Erro...

Problem with message routing between EXCH2K and EX2K3
We are running a ver simply Exchange topology, with an Exchange 2000 single server (ServerA) acting as "dumb" Information Store. Incoming and outbound SMTP email is relayed through a Linux based SMTP host relay on the same subnet, which handles all domain lookups for mail routing. ServerA is configured to use to the Linux SMTP server as it's smart host. The domain is a native Windows 2003 AD topology. My problem is the following: I have recently added a new Exchange 2003 Server (ServerB) into our Exchange site topology. If I create a user (UserA) on ServerA and then do a...

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...