Charts
 

I am writing a macro that calculates certain values in rows. Now I want to
make the macro automatically construct a predefined chart displaying the
information in the rows. Help!! Please!!
--
I am very thankful for any fast assistance!

Charts
 

Hi Samantha,

Try turning on the macro recorder while you create the chart manually. The
resultant code may be edited to enable more general application.

If you experience problems with such editing, post back with the problematic
code.


---
Regards,
Norman


"SamanthaK" wrote in message
...
I am writing a macro that calculates certain values in rows. Now I want to
make the macro automatically construct a predefined chart displaying the
information in the rows. Help!! Please!!
--
I am very thankful for any fast assistance!

Charts
 

Thank you. However, the cells that I want to calculate are not the same every
time. It works like this: I copy information into the Excel-spreadsheet from
another program. The information is somewhat different every time although
certain types of info is required. I then search the headings in order to
identity the column that contains the desired data for a certain calculation.
E.g. I have market value (for a futures-contract) in one column and the date
in another column. The date in a certain row corresponds to the market value
in the same row. However the infor may be pasted into Excel at different
places every time so that row1 does not necessearlity imply a heading nor
does coulmn1 not imply e.g. date. I have managed to find the headings and
calculate the length the column. Now I want to make a predfined chart based
on the values. The problem is that the start and end of the coulmn as well as
the coulmn itself is variable. (sorry for the long text :-)) Can you help me
one this one please!!!.
--
I am very thankful for any fast assistance!


"Norman Jones" skrev:

Hi Samantha,

Try turning on the macro recorder while you create the chart manually. The
resultant code may be edited to enable more general application.

If you experience problems with such editing, post back with the problematic
code.


---
Regards,
Norman


"SamanthaK" wrote in message
...
I am writing a macro that calculates certain values in rows. Now I want to
make the macro automatically construct a predefined chart displaying the
information in the rows. Help!! Please!!
--
I am very thankful for any fast assistance!

Charts
 

Hi Samantha,

Try something like:

'=============
Public Sub Tester004()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim FirstCell As Range
Dim LastCell As Range
Dim myChart As Object
Const myHeader As String = "Market Value" '<<===== CHANGE

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet3") '<<===== CHANGE

Set FirstCell = SH.Cells.Find(What:=myHeader, After:=SH.Range("A1"))
Set LastCell = SH.Cells(Rows.Count, FirstCell.Column).End(xlUp)
Set rng = SH.Range(FirstCell, LastCell).Offset(0, -1).Resize(, 2)

Call GraphIt(SH, rng)

End Sub
'<<=============


'=============
Public Sub GraphIt(SH As Worksheet, rng As Range)
Dim CHObj As ChartObject

Set CHObj = SH.ChartObjects.Add(Left:=200, Top:=100, _
Width:=300, Height:=200) '<<=== CHANGE

With CHObj.Chart
.ChartType = xlLineMarkers
.SetSourceData Source:=rng, PlotBy _
:=xlColumns
.SeriesCollection(1).Name = "=""Values""" '<<=== CHANGE
.HasTitle = True
.ChartTitle.Characters.Text = "Market Data" '<<=== CHANGE

With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Date" '<<=== CHANGE
End With

With .Axes(xlValue, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Value" '<<=== CHANGE
End With
End With

End Sub
'<<=============

--
---
Regards,
Norman



"SamanthaK" wrote in message
...
Thank you. However, the cells that I want to calculate are not the same
every
time. It works like this: I copy information into the Excel-spreadsheet
from
another program. The information is somewhat different every time although
certain types of info is required. I then search the headings in order to
identity the column that contains the desired data for a certain
calculation.
E.g. I have market value (for a futures-contract) in one column and the
date
in another column. The date in a certain row corresponds to the market
value
in the same row. However the infor may be pasted into Excel at different
places every time so that row1 does not necessearlity imply a heading nor
does coulmn1 not imply e.g. date. I have managed to find the headings and
calculate the length the column. Now I want to make a predfined chart
based
on the values. The problem is that the start and end of the coulmn as well
as
the coulmn itself is variable. (sorry for the long text :-)) Can you help
me
one this one please!!!.
--
I am very thankful for any fast assistance!


"Norman Jones" skrev:

Hi Samantha,

Try turning on the macro recorder while you create the chart manually.
The
resultant code may be edited to enable more general application.

If you experience problems with such editing, post back with the
problematic
code.


---
Regards,
Norman


"SamanthaK" wrote in message
...
I am writing a macro that calculates certain values in rows. Now I want
to
make the macro automatically construct a predefined chart displaying
the
information in the rows. Help!! Please!!
--
I am very thankful for any fast assistance!