# how do we get average of cells in excel?

Page 1 of 1

## 0 Replies - 1171 Views - Last Post: 09 November 2009 - 07:39 PMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'http://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=137936&amp;s=d9f52394f5422b2eda8961bf1bb0e6cd&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 renmee

Reputation: 5
• Posts: 155
• Joined: 20-July 09

# how do we get average of cells in excel?

Posted 09 November 2009 - 07:39 PM

This is my whole code i just need to have a code to get the computed average of cells in excel worksheet.
im using it to plot a chart....
Thanks for your future help guys....

```Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

Private Function GetIniFileVal(ByVal section As String, ByVal key As String) As String
Dim buff As String
Dim ret As Long

buff = Space\$(255)

ret = GetPrivateProfileString(section, key, "", buff, 255, App.Path & "\Graph.ini")

If ret = 0 Then
GetIniFileVal = ""
Else
GetIniFileVal = Left(buff, ret)
End If

End Function

Sub Main()

Dim ExlApp 'As Excel.Application
Dim Exlwbk 'As Excel.Workbook
Dim Exlsht 'As Excel.Worksheet
Dim max, min, maj, thick

On errror GoTo ExlError

Set ExlApp = New Excel.Application
Set Exlwbk = ExlApp.Workbooks.Open("C:\QA Reports\temp_subweight.xls")
Set Exlsht = Exlwbk.Sheets(1)

'Form1.Show
'Form1.MousePointer = vbHourglass

Exlsht.Activate

Exlsht.Range("F2").Select
ExlApp.Selection.Copy
Exlsht.Range("F1").Select
ExlApp.ActiveSheet.Paste
Exlsht.Rows("2:2").Select
ExlApp.Application.CutCopyMode = False
ExlApp.Selection.Delete Shift:=xlUp

thick = Split(Range("B2").Text, "-")(2)

max = GetIniFileVal("MAX", thick)
min = GetIniFileVal("MIN", thick)
maj = GetIniFileVal("MAJ_UNIT", thick)

'insert logo
ActiveSheet.Shapes("Picture 1").Select
Selection.Delete
'Exlsht.Range("A1").Select
'ActiveSheet.Pictures.Insert (App.Path & "\nikko3.jpg")
'ActiveSheet.Shapes("Picture 1").Select

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("C12:G26").Select
Selection.ClearContents
Range("12:12,14:14,16:16,18:18,20:20,22:22,24:24,26:26").Select
Range("A26").Activate
Selection.Delete Shift:=xlUp
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Rows("7:7").Select
Selection.Insert Shift:=xlDown
Rows("9:9").Select
Selection.Insert Shift:=xlDown
Rows("11:11").Select
Selection.Insert Shift:=xlDown
Rows("13:13").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A16:B24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("A15").Select
Selection.Font.Underline = xlUnderlineStyleSingle
Range("E4").Select
Selection.Copy
Range("D4").Select
ActiveSheet.Paste
Range("E4").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("F4").Select
Selection.Copy
Range("E4").Select
ActiveSheet.Paste
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Range("E8").Select
ActiveSheet.Paste
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Range("E10").Select
ActiveSheet.Paste
Range("F12").Select
Application.CutCopyMode = False
Selection.Copy
Range("E12").Select
ActiveSheet.Paste
Range("F4").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("F8").Select
Selection.ClearContents
Range("F10").Select
Selection.ClearContents
Range("F12").Select
Selection.ClearContents
Range("H10").Select
Selection.Copy
Range("G10").Select
ActiveSheet.Paste
Range("H10").Select
Application.CutCopyMode = False
Selection.ClearContents
Columns("C:C").ColumnWidth = 0.92
Columns("F:F").ColumnWidth = 17.14
Range("A3:K3").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Rows("3:3").RowHeight = 33.75
With Selection.Font
.Name = "Arial"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("G13").Select
Columns("F:F").ColumnWidth = 4
Columns("B:B").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").ColumnWidth = 0.08
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Range("M3").Select
Selection.Copy
Range("M2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("M3").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("M2").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("M1").Select
ActiveCell.FormulaR1C1 = "Date Printed:" & Now()
Range("M25").Select
Selection.Copy
Range("M34").Select
ActiveSheet.Paste
Range("M25").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("M34").Select
Selection.Font.Bold = False
Selection.Font.Italic = True
Columns("M:M").ColumnWidth = 16.29
Columns("M:M").EntireColumn.AutoFit
Range("M2").Select
Selection.Font.Bold = False
Range( _
"M4,M6,M8,M10,K12,K10,K8,K6,K4,I4,I6,I8,I10,E12,E10,E8,E6,E4,B4,B6,B8,B10,B12") _
.Select
Range("B12").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Columns("M:M").ColumnWidth = 23.57
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("temp_subweight").Range("B19:B24"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="temp_subweight"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "BCF Subweight Measurement Graphical View"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "No. of Samples"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "UCL/LCL"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.ChartTitle.Select
Activewindow.Visible = False
Windows("temp_subweight.xls").Activate
Range("D9").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 2").IncrementLeft -15#
ActiveSheet.Shapes("Chart 2").IncrementTop 84.75
Activewindow.Visible = False
Windows("temp_subweight.xls").Activate
Range("M34").Select
Selection.Copy
Activewindow.SmallScroll Down:=6
Range("M39").Select
ActiveSheet.Paste
Range("M34").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 2").ScaleWidth 1.44, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 2").ScaleHeight 1.09, msoFalse, msoScaleFromTopLeft
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 7
End With
ActiveChart.SeriesCollection(1).Points(5).Select
Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection.Border
.ColorIndex = 55
.Weight = xlThin
.LineStyle = xlContinuous
End With
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).MinorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlValue).MajorGridlines.Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Val(min)
.MaximumScale = Val(max)
'.MinorUnit = 1
.MajorUnit = Val(maj)
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).MinorGridlines.Select
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
Selection.Top = 24
Selection.Height = 215
Selection.Height = 222
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Top = 250
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinorGridlines.Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.ChartTitle.Select
ActiveChart.ChartArea.Select
Windows("temp_subweight.xls").SmallScroll Down:=-18
Windows("temp_subweight.xls").Activate
Range("G9").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 2
.LineStyle = -1
End With
Selection.Interior.ColorIndex = xlAutomatic
Sheets("temp_subweight").DrawingObjects("Chart 2").RoundedCorners = True

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 2").IncrementLeft 43.5
ActiveSheet.Shapes("Chart 2").IncrementTop 6#
ActiveSheet.Shapes("Chart 2").IncrementLeft -25.5
Windows("temp_subweight.xls").ScrollColumn = 2
ActiveSheet.Shapes("Chart 2").ScaleWidth 1.09, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Left = 279
Activewindow.Visible = False
Windows("temp_subweight.xls").Activate
Range("J36").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlCategory).Select
Activewindow.Visible = False
Windows("temp_subweight.xls").Activate
Range("L37").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlCategory).Select
Activewindow.Visible = False
Windows("temp_subweight.xls").Activate
Range("J36").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 2").ScaleHeight 1.07, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlCategory).MinorGridlines.Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
Selection.Height = 237
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Top = 261
ActiveChart.ChartArea.Select
Windows("temp_subweight.xls").ScrollColumn = 1
Windows("temp_subweight.xls").SmallScroll Down:=-18
Activewindow.Visible = False
Windows("temp_subweight.xls").Activate
Range("A1").Select
ActiveSheet.Pictures.Insert(App.Path & "\metals-name.bmp").Select
Selection.ShapeRange.ScaleWidth 0.49, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.49, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.83, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.82, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.82, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.82, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.9, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.91, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementLeft 19.5
Range("A3:M3").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
End With
Selection.Merge
ActiveSheet.Shapes("Picture 3").Select
Selection.ShapeRange.IncrementTop 0.75
Selection.ShapeRange.IncrementTop 0.75
Selection.ShapeRange.IncrementTop 0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementTop 0.75
Selection.ShapeRange.IncrementTop 0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementTop 0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementTop -0.75
Selection.ShapeRange.ScaleWidth 1.12, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.12, msoFalse, msoScaleFromTopLeft

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintGridlines = False
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Range("E4,E6,E8,E10,E12,I4,I6,I8,I10,K12,K10,K8,K6,K4").Select
Range("K4").Activate
Selection.NumberFormat = "0.00"
Range("I8,I10").Select
Range("I10").Activate
Selection.NumberFormat = "0.0000"
Activewindow.ScrollRow = 5
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
Selection.Height = 230
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Top = 263
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
Selection.Height = 236

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
Selection.Height = 234
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Left = 291
Selection.Top = 261

Range("E4,E6,E10,E12").Select
Range("E12").Activate
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("E:E").EntireColumn.AutoFit
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
Selection.Height = 219
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Left = 295
Selection.Top = 260
ActiveChart.PlotArea.Select
Selection.Height = 234

ActiveChart.ChartTitle.Select
Selection.Top = 2
ActiveChart.PlotArea.Select
Selection.Left = 27
Selection.Top = 18

Range("L10:M10").Select
Selection.Copy
Range("L12").Select
ActiveSheet.Paste
Range("L12").Select
Application.CutCopyMode = False
With ActiveCell.Characters(Start:=1, Length:=12).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("M12").Select
Selection.ClearContents

If Range("A25") = 9 Then
Range("26:26,28:28,30:30,32:32,34:34,36:36,38:38,40:40").Select
Range("A40").Activate
Selection.Delete Shift:=xlUp
Range("A25:B30").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("temp_subweight").Range("B19:B30"), _
PlotBy:=xlColumns
Range("A33:D37").Select
Selection.ClearContents

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 2").ScaleHeight 1.34, msoFalse, msoScaleFromTopLeft
Else
Range("A25:D30").Select
Selection.ClearContents

Range("M30").Select
Selection.Cut
Range("M39").Select
ActiveSheet.Paste
End If

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "General"
Range("B19:B30").Select
Selection.NumberFormat = "0.00"

Range("A17:B18").Select
Selection.ClearContents
Range("A31:B32").Select
Selection.ClearContents
Range("E4").Select
Range("A17:B18").Calculate
Range("A17:B18").Delete

Range("A1").Select
Exlwbk.Save
ExlApp.Visible = True

Set ExlApp = Nothing
Set Exlwbk = Nothing
Set Exlsht = Nothing

End
Exit Sub
ExlError:
MsgBox "Error: " & Err.Description, vbCritical
Err.Clear
End
End Sub

```

This post has been edited by renmee: 09 November 2009 - 08:20 PM

Is This A Good Question/Topic? 0

Page 1 of 1

 .related ul { list-style-type: circle; font-size: 12px; font-weight: bold; } .related li { margin-bottom: 5px; background-position: left 7px !important; margin-left: -35px; } .related h2 { font-size: 18px; font-weight: bold; } .related a { color: blue; }