Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Error 1004, several smaller questions :)

Below is my code. My problem is when LastRow is greater than 300 I get
unable to set xvalues property error 1004. Any help ?

Also I can't seem to get the xvalues to display on the chart, I get the correct number of tick marks but no numbers to go with them. Any ideas ?

One more small problem, at the bottom my code should remove excel from the memory, but if I check in the task manager excel is still running, how do I remove it from memory ?

I would award more points but running low :)

Option Explicit

Dim varNum As Long
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim LastRow As Long
Dim B3 As Long

Public Sub Data()

On Error Resume Next

'Start the excel COM and make it visible.
Set objExcel = GetObject("", "excel.application")
'Set objExcel = excel.Application ' Seems to cause a memory leak
objExcel.Visible = False
   
'Start a workbook.

Set objWorkbook = objExcel.Workbooks.Add

'Turn off the alerts, otherwise user will have to confirm actions.
objExcel.DisplayAlerts = False

'Ensure there is only one worksheet.
Do While objWorkbook.Worksheets.Count > 1
    Set objWorksheet = objWorkbook.Worksheets.Item(objWorkbook.Worksheets.Count)
    objWorksheet.Delete
Loop

'Set objWorksheet to the remaining worksheet.
Set objWorksheet = ActiveSheet

'Rename the sheet to Results.
objWorksheet.Name = "Results"


'Enter data
With objWorksheet.QueryTables.Add(Connection:="text;c:\WINDOWS\desktop\TRIAL.txt", Destination:=Range("A3"))
    .Name = "Results"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .Refresh BackgroundQuery:=False
End With
   

Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row

'Draw Chart with the data
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C" & LastRow & ":C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Results" ' Title
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Number of Cycles" ' X-Axis
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Root Mean Square Distance "   ' Y-Axis
End With
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.Shapes("Chart 1")
    .Left = 50
    .Top = 180
End With

'Headers
objWorksheet.Cells(1, 1) = "Number of Cycles"
objWorksheet.Cells(1, 1).Font.Bold = True
objWorksheet.Cells(1, 2) = "Root Mean Square Distance"
objWorksheet.Cells(1, 2).Font.Bold = True

Range("A:A", "B:B").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
End With

Range("A:A", "B:B").Select
Selection.Columns.AutoFit
objExcel.Visible = True

'Turn back on alerts
objExcel.DisplayAlerts = True

'Free up memory
Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
End Sub
0
Praetor
Asked:
Praetor
  • 18
  • 15
1 Solution
 
samopalCommented:
It seems to me you understand my rejected answer http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=10310155  without any comments :-)))
0
 
wesleystewartCommented:
In the line:

ActiveChart.SeriesCollection(1).XValues = "=Results!R3C" & LastRow & ":C1"

You are building a formula with the value of Lastrow.  That's fine, but you can only have 256 columns in a worksheet, and in this case (with Lastrow=300) the formula would say:

=Results!R3C300:C1

Which is invalid.  I suspect it won't if Lastrow is > 256.

Wes
0
 
PraetorAuthor Commented:
Samopal. To be fair if u look at your answer it's a long way from what I needed. I admit I should have commented...sorry :)

Anyway can u help me out with this ?

Sorry for not commenting on your help :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wesleystewartCommented:
Hmm.  I thought those were R1C1 coordinates.  I guess that's a swing and a miss...

Wes
0
 
PraetorAuthor Commented:
WesleyStewart I think I understand what I have done wrong, you haven't answered my question but u have certainly focussed my attention on the offending code.

I meant to have

ActiveChart.SeriesCollection(1).XValues = "=Results!R3C2:" & LastRow & "C1"


0
 
wesleystewartCommented:
Well, something led me to that line of code.....:^)
0
 
PraetorAuthor Commented:
Still need help on points 1 & 2
0
 
PraetorAuthor Commented:
I meant I still need help on points 2 & 3
0
 
wesleystewartCommented:
If the code fails before it gets to the end, you won't execute the lines where you set Excel to nothing.

Use the New keyword rather than Getobject:

Set objExcel = New Excel.Application

Wes
0
 
wesleystewartCommented:
I think you need something along the lines of:

ActiveChart.ApplyDataLabels

to get the labels to show.

Wes
0
 
PraetorAuthor Commented:
WesleyStewart, sorry you misunderstood me, I don't want data labels on the chart, I want the xValues to be on the xaxis.

say my xvalues were 1,2,3,4,5 then on the axis I want the numbers 1,2,3,4,5.
0
 
PraetorAuthor Commented:
Adjusted points from 50 to 60
0
 
wesleystewartCommented:
Yes, but we are hot on it's trail....

ActiveCHart.ApplyDataLabels shows the data labels for the chart.  What you need is to reference the data series for which you want the labels to show.  Try this:

ActiveChart.SeriesCollection(1).ApplyDataLabels

or

ActiveChart.SeriesCollection(2).Apply
DataLabels

The trick will be returning a reference to the X-Axis series, which might be SeriesCollection(1) or might not be.  From Excel Help:

The series index number indicates the order in which the series were added to the chart. SeriesCollection(1) is the first series added to the chart, and SeriesCollection(SeriesCollection.Count) is the last one added.

I think we're almost there.


Wes
0
 
PraetorAuthor Commented:
Adjusted points from 60 to 70
0
 
PraetorAuthor Commented:
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C" & LastColumn & ":C1"

Why doesn't this work ??
0
 
wesleystewartCommented:
I think your syntax is off.  Assuming Results is the active worksheet, I think the line should read:

ActiveChart.SeriesCollection(1).XValues = Worksheets(Results).Range("R3C" & LastColumn & ":C1")

or something to that effect.  Sorry I can't be more sure, but the syntax involving a variable is hard for me to figure out. I think your use of the "=" is off:

).XValues = "=Results!R3C"

because you don't need an "=" sign in the property.  Take a look at the help sample.  If you don't know how to find it:  Click on the object browser, type 'xvalues' in the lower combo box, and hit enter.  When you see 'xvalues' in the 'members' window in the right-hand side of the object browser, click on it and hit F1.

Wes

0
 
PraetorAuthor Commented:
What is weird though is if instead of having LastColumn I put in R1C1 co-ordinates it works fine. Problem is these would have to be specified at design time, and I need them to be determined at run time.

ActiveChart.SeriesCollection(1).XValues = "=Results!R3" & LastColumn "& :C1"

This is the offending line of code

Please someone help me !!
0
 
wesleystewartCommented:
Where did LastColumn come from?  It is neither declared nor set to a value in your code above.  That right there could be the problem.

I have run some tests using strings to set the XValues property using the Range object.  It seems to work pretty well.  I'm having trouble getting R1C1 to work.  Can you paste a line that does work so I can see it?

Sorry this is taking so long.  This is quite a challenge.

Wes
0
 
PraetorAuthor Commented:
ok this is all the relevant module. I need this to work. WS thank for all your help. Hopefully get this sorted soon :)

Option Explicit

Dim varNum As Long
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim B3 As Long

Public Sub Data()

On Error Resume Next

'Start the excel COM and make it visible.
Set objExcel = GetObject("", "excel.application")
'Set objExcel = excel.Application ' Seems to cause a memory leak

objExcel.Visible = False
   
'Start a workbook.

Set objWorkbook = objExcel.Workbooks.Add

'Turn off the alerts, otherwise user will have to confirm actions.
objExcel.DisplayAlerts = False

'Ensure there is only one worksheet.
Do While objWorkbook.Worksheets.Count > 1
    Set objWorksheet = objWorkbook.Worksheets.Item(objWorkbook.Worksheets.Count)
    objWorksheet.Delete
Loop

'Set objWorksheet to the remaining worksheet.
Set objWorksheet = ActiveSheet

'Rename the sheet to Results.
objWorksheet.Name = "Results"

'Enter data
With objWorksheet.QueryTables.Add(Connection:="text;C:\WINDOWS\desktop\TRIAL.txt", Destination:=Range("A3"))
    .Name = "Results"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .Refresh BackgroundQuery:=False
End With
   
Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row
LastColumn = Selection.Column

'Draw Chart with the data
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Results!R3" & LastColumn & ":C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Results" ' Title
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Number of Cycles" ' X-Axis
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Root Mean Square Distance "   ' Y-Axis
End With
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.Shapes("Chart 1")
    .Left = 50
    .Top = 180
End With

'Headers
objWorksheet.Cells(1, 1) = "Number of Cycles"
objWorksheet.Cells(1, 1).Font.Bold = True
objWorksheet.Cells(1, 2) = "Root Mean Square Distance"
objWorksheet.Cells(1, 2).Font.Bold = True


Range("A:A", "B:B").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
End With

Range("A:A", "B:B").Select
Selection.Columns.AutoFit
objExcel.Visible = True

'Turn back on alerts
objExcel.DisplayAlerts = True

'Free up memory
Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
End Sub

0
 
wesleystewartCommented:
replace the troubled line with:

ActiveChart.SeriesCollection(1).XValues = ("Results!R3" & Lastcolumn & ":C1")

and give it a shot.  It works fine on my machine.  

One note:  In your code you are hardcoding LastRow and LastColumn in this section:

Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row
LastColumn = Selection.Column

Because your range is only one cell, LastRow will always be 4, and LastColumn will always be 2.

Wes
0
 
PraetorAuthor Commented:
please help me......below entire code for relevant section.








Option Explicit

Private Declare Function SendMessage Lib "User32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal Param As Long, lParam As Any) As Long
Dim N As Integer, M As Integer, x As Integer, Y As Integer, R As Integer, j As Integer
Dim i As Integer, z As Long, xproposed As Integer, yproposed As Integer, rand As Integer
Dim xdist As Integer, ydist As Integer, totdist As Integer, savecounter As Integer
Dim R1 As Integer, R2 As Integer, vectorR As Integer
Dim spacial(), xpoly(), ypoly()
Dim c As Long, counter As Long
Dim intM As String
Dim strNmsg As String
Dim strMmsg As String
Dim xrand As Single, yrand As Single

Private Sub cmdstart_Click()

On Error Resume Next

N = Val(txtchains.Text)
M = Val(txtarray.Text)
savecounter = Val(txtsavecount.Text)

If M >= 2 * N Then
    ReDim spacial(1 To M, 1 To M)
    Else
        MsgBox "m must be greater or equal to 2*n"
    Exit Sub
End If
   
ReDim xpoly(0 To N)
xpoly(1) = M / 2            'sets x co-ordinate of 1st bead

ReDim ypoly(0 To N)
ypoly(1) = M / 2            'sets y co-ordinate of 1st bead

For i = 2 To N Step 2
    xpoly(i) = xpoly(i - 1)            'move bead i, o step in x direction
    ypoly(i) = ypoly(i - 1) + 1       'move bead i, 1 step in y direction
    xpoly(i + 1) = xpoly(i) + 1      'move bead i+1, 1 step in x direction
    ypoly(i + 1) = ypoly(i)             'move bead i+1, 0 step in y direction
Next i
 
For i = 1 To N
    spacial(xpoly(i), ypoly(i)) = 1
Next i

c = Val(txtnumbercycles.Text)   'number of cycles for progamme to execute

counter = 0

For z = 1 To c          'loop for specified number of cycles
   
Call cal
   
Open "C:\WINDOWS\desktop\TRIAL.txt" For Append As #1   'writes z and vectorR to specified file
Write #1, z, vectorR
Close #1
   
txtcycle.Text = Format(z)
txtcycle.Refresh

Next z

Call Data 'need to add call data when routine finished

End Sub

Public Sub cal()
   
1   R = Int((N * Rnd) + 1)  'create random integer R between 1 and N inclusive
   If R = N Or R = 1 Then
        xrand = Rnd
        If xrand > 0.5 Then
            x = 1
        Else                'create random move in x direction
            x = -1
        End If
   
        yrand = Rnd         'create random move in y direction
        If yrand > 0.5 Then
            Y = 1
        Else
            Y = -1
        End If
   Else
        GoTo 2
   End If
 
       
   xproposed = xpoly(R) + x       'add random x move onto original x
   yproposed = ypoly(R) + Y       'add random y move onto original y

   If spacial(xproposed, yproposed) = 1 Then    'checks to see if proposed co-ordinates are occupied
       GoTo 4
   End If
           
   If R = N Then
       xdist = xproposed - xpoly(R - 1)
       ydist = yproposed - ypoly(R - 1)
       End If
   
   If R = 1 Then
       xdist = xproposed - xpoly(2)
       ydist = yproposed - ypoly(2)
   End If
   
   totdist = ((xdist) ^ 2) + ((ydist) ^ 2)
       
   If totdist <> 1 Then   'checks to see if nearest neighbour distance when moved will still equal 1
       GoTo 4
   Else
       spacial(xpoly(R), ypoly(R)) = 0      'clears original position in array
       xpoly(R) = xproposed
       ypoly(R) = yproposed
       spacial(xpoly(R), ypoly(R)) = 1  'update spacial array with new position
       Call edge
   End If
   
   GoTo 4
   
'Changed xrand and yrand to single, cause that's what Rnd function returns
'Rnd returns a random number between 0 and 1, so if it's an integer it would allways be
'either 0 or 1, most likely 1 cause an integer will always round up

2   xrand = Rnd                  'generate random number
      If xrand > 0.5 Then
          x = 1
      Else
          x = (-1)
      End If
       
   yrand = Rnd
   If yrand > 0.5 Then
      Y = 1
   Else
      Y = -1
   End If
         
   xproposed = xpoly(R) + x   'add random x move onto original x
   yproposed = ypoly(R) + Y   'add random y move onto original y

   If spacial(xproposed, yproposed) = 1 Then   'check to see if lattice position occupied
       GoTo 4
   End If
   
   xdist = xproposed - xpoly(R - 1)
   ydist = yproposed - ypoly(R - 1)

   totdist = ((xdist) ^ 2) + ((ydist) ^ 2)
   If totdist <> 1 Then   'check to see if unit distance from first nearest neighbours is maintained
        GoTo 4
   End If
   
   xdist = xproposed - xpoly(R + 1)
   ydist = yproposed - ypoly(R + 1)
   totdist = ((xdist) ^ 2) + ((ydist) ^ 2)
   If totdist <> 1 Then    'check to see if unit distance from second nearest neighbours is maintained
       GoTo 4
   End If
   
   spacial(xpoly(R), ypoly(R)) = 0  'sets original co-ordinates in spacial array to 0
   xpoly(R) = xproposed
   ypoly(R) = yproposed
   spacial(xpoly(R), ypoly(R)) = 1  'sets new co-ordinates in spacial arrray to 1
   Call edge
   
4  counter = counter + 1 'add one to the counter
   If counter = savecounter Then  'checks to see if every bead in chain has been moved
        counter = 1
        R1 = xpoly(N) - xpoly(1)
        R2 = ypoly(N) - ypoly(1)
        vectorR = (((R1) ^ 2) + ((R2) ^ 2)) 'calculate length of vector between first bead & end bead
        txtvector.Text = Format(vectorR)
        txtvector.Refresh
   Else
        GoTo 1
   End If
           
End Sub

Public Sub Form_Load()

SendMessage cmdstart.hWnd, &HF4&, &H0&, 0&  'changes cmdstart button to C++ style

Call FileExist("C:\Program Files\Microsoft Office\Office\EXCEL.EXE")

Call FileExist1("C:\WINDOWS\desktop\TRIAL.txt")

End Sub

Private Sub txtchains_LostFocus()

N = Val(txtchains.Text)
    If N Mod 2 = 0 Then
        MsgBox "Chains must be an odd number!"
        txtchains.SetFocus
    Else
        Exit Sub
    End If

End Sub

Private Sub txtarray_LostFocus()

M = Val(txtarray.Text)
If M Mod 2 <> 0 Then
    MsgBox "Array must be an even number!"
    txtarray.SetFocus
    Else
        Exit Sub
End If

End Sub

Public Sub edge()

Dim xmove As Integer, ymove As Integer

'checks to see if either x or y co-ordinates approach the edge of the array
'if they do calculates the distance between the centre of the array and the
'centre bead and then moves all the beads back by the same distance

If xpoly(R) = 1 Or xpoly(R) = M Or ypoly(R) = 1 Or ypoly(R) = M Then
    xmove = xpoly((N + 1) / 2) - (M / 2)
    ymove = ypoly((N + 1) / 2) - (M / 2)
    For i = 1 To N
        spacial(xpoly(i), ypoly(i)) = 0
        xpoly(i) = xpoly(i) - xmove
        ypoly(i) = ypoly(i) - ymove
        spacial(xpoly(i), ypoly(i)) = 1
    Next i
End If

End Sub

Public Function FileExist(filename As String) As Integer

Dim q As Integer

q = Len(Dir$(filename))  'checks to see if installed copy of excel
If q = 0 Then
    MsgBox "This program requires a Working copy of Excel"
    Unload Me           'if excel not found program won't execute
Else
    Exit Function
End If

End Function

Public Function FileExist1(filename As String) As Integer

Dim q As Integer

q = Len(Dir$(filename))  'checks for file
If q = 0 Then
    Exit Function
Else
    Kill (filename)     'if file exists deletes it
End If

End Function


next bit in seperate module




Option Explicit

Dim varNum As Long
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim B3 As Long

Public Sub Data()

On Error Resume Next

'Start the excel COM and make it visible.
Set objExcel = GetObject("", "excel.application")
'Set objExcel = excel.Application ' Seems to cause a memory leak

objExcel.Visible = False
   
'Start a workbook.

Set objWorkbook = objExcel.Workbooks.Add

'Turn off the alerts, otherwise user will have to confirm actions.
objExcel.DisplayAlerts = False

'Ensure there is only one worksheet.
Do While objWorkbook.Worksheets.Count > 1
    Set objWorksheet = objWorkbook.Worksheets.Item(objWorkbook.Worksheets.Count)
    objWorksheet.Delete
Loop

'Set objWorksheet to the remaining worksheet.
Set objWorksheet = ActiveSheet

'Rename the sheet to Results.
objWorksheet.Name = "Results"

'Enter data
With objWorksheet.QueryTables.Add(Connection:="text;C:\WINDOWS\desktop\TRIAL.txt", Destination:=Range("A3"))
    .Name = "Results"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .Refresh BackgroundQuery:=False
End With
   
Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row
LastColumn = Selection.Column

'Draw Chart with the data
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Results" ' Title
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Number of Cycles" ' X-Axis
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Root Mean Square Distance "   ' Y-Axis
End With
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.Shapes("Chart 1")
    .Left = 50
    .Top = 180
End With

'Headers
objWorksheet.Cells(1, 1) = "Number of Cycles"
objWorksheet.Cells(1, 1).Font.Bold = True
objWorksheet.Cells(1, 2) = "Root Mean Square Distance"
objWorksheet.Cells(1, 2).Font.Bold = True


Range("A:A", "B:B").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
End With

Range("A:A", "B:B").Select
Selection.Columns.AutoFit
objExcel.Visible = True

'Turn back on alerts
objExcel.DisplayAlerts = True

'Free up memory
Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
End Sub

0
 
PraetorAuthor Commented:
Adjusted points from 70 to 110
0
 
wesleystewartCommented:
LastRow and LastColumn seem to be switching places in your code, but if the line that doesn't work is still:

ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"

Then replace that line with:

ActiveChart.SeriesCollection(1).XValues = ("Results!R3" & LastRow & ":C1")

and see if it works.  Otherwise, are you still encountering the same problem?  The Data() procedure works on my machine.  I can't test the whole thing without creating a bunch of files . . .

Wes
0
 
PraetorAuthor Commented:
yeah I agree it works but it doesn't do what I want it to do.

On the x axis it reads:
Results!R32:C1

I want it to be numbered

example
if you plotted y values against x values and the x values were 1,2,3,4,5

on the chart u would expect tick marks and 1,2,3,4,5 next to these tick marks, and at every tick mark there would be a point on the chart corresponding to that y value.

That is what I require.

WS we'll get there in the end, it's always the easy problems that r the most difficult so sort...give me an mapi problem anyday :)
0
 
wesleystewartCommented:
On my chart the data points are labeled on the chart but not on the lower scale.  To get rid of the "Results!R32:C1" at the bottom of the chart, just comment out this line:

ActiveChart.SeriesCollection(1).XValues = ("Results!R3" & Lastcolumn & ":C1")

It looks like this has been the problem all the time.  I don't know what setting the XValues property does, but apparently it doesn't affect the chart data.  I might be missing something . . .

Replace these lines of Data():

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"

with:

Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & Lastrow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).ApplyDataLabels

Wes
0
 
PraetorAuthor Commented:
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"

this is the line that should make my values in column 1 the x axis. I need this line in the code, problem is not sure of the syntax. If I specify actual R1C1 co-ordinates it works fine. eg

ActiveChart.SeriesCollection 1).XValues="=Results!R3C1:R103C1"

I need to replace R103 by the row that the data goes down to and that is only determined at run time !!

This is really beginning to do my head in :)
0
 
wesleystewartCommented:
Please let me repeat:

That line does not affect the chart data in this particular code.  That line does not set the X-axis.  This line sets the X-axis and Y-axis data:

ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns

Paste it in and give it a shot.

Wes
0
 
PraetorAuthor Commented:
ActiveChart.SeriesCollection 1).XValues="=Results!R3C1:R103C1"

this code most definitely sets the numbers to be displayed on the x axis !!  I have just tried it. It takes my data in the 1st column and places these values along the x axis.

I can't take much more....
0
 
wesleystewartCommented:
First:  Did you try it like I suggested yet?  Please leave out that line and try running the code.

Just to make sure I understand:

Since your data is only one column wide (:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns) then how can you get data labels on the Y (up and down) and X (left to right) axes?  I have labels next to each data point, and in addition the Y-axis is scaled.  On the X-axis I have each data's ordinal number, as in the first data point is "1", the next is "2", and so on.

Let's say I have only 2 data, "10" and "12"

Test case:

On my chart the Y-axis is scaled from 9 to 12.5.  The X-axis has a "1" and a "2", numbering each data point.  Next to each data point is the data label.  In this case would you want a "10" and a "12" at the bottom (X-axis) also?

Wes
0
 
PraetorAuthor Commented:
no, my data is 2 columns wide. The first column are the y values. Second the x values. I want the x ordinal numbers changed to the data in the 1st column.

as far as I was aware:

ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"

specified the range for the x values in cloumn 1....maybe all my code is bs :)
0
 
wesleystewartCommented:
Your code is setting your chart to one column.

This is one column:

Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row
LastColumn = Selection.Column

and this is one column:

ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns

Yes, in that case your code is screwed.  I suggest you get some Excel VBA books and start from scratch.

Wes
0
 
PraetorAuthor Commented:
Wes, I have sorted it all I needed to do was to alter the formatting of the x axis..........thank fcuk for that :)

if u can sort out this the points r yours :

One more small problem, at the bottom my code should remove excel from the memory, but if I check in the task manager excel is still running, how do I remove it from memory ?



0
 
wesleystewartCommented:
I actually believe I already answered that part, but to summarize:

First:  Set Excel objects to nothing at the end of the code:

Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing

You have to do this for EVERY procedure that instantiates them.

Next:  Be aware that if the code fails PRIOR to reaching the above lines in code, it won't clear out the objects from memory and they will stay in the taskbar.

Your Sub Data() works properly as far as objects are concerned.  Is there any chance that you have any other apps running that are creating Excel objects?
0
 
PraetorAuthor Commented:
Oh fcuk it, you've helped me enough, here r your points. Thanks for all your help. Got there in the end :)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 18
  • 15
Tackle projects and never again get stuck behind a technical roadblock.
Join Now