0 Replies - 1017 Views - Last Post: 09 November 2009 - 07:39 PM Rate Topic: -----

#1 renmee  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • 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
		.AddIndent = False
		.ShrinkToFit = False
		.MergeCells = False
	End With
	With Selection
		.HorizontalAlignment = xlGeneral
		.WrapText = True
		.Orientation = 0
		.AddIndent = False
		.ShrinkToFit = False
		.MergeCells = False
	End With
	With Selection
		.HorizontalAlignment = xlCenter
		.WrapText = True
		.Orientation = 0
		.AddIndent = False
		.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
		.AddIndent = False
		.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
		.Shadow = 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
		.Shadow = 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
	Charts.Add
	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
		.Shadow = False
	End With
	ActiveChart.SeriesCollection(1).Points(5).Select
	ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
		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
		.Shadow = 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
		.Shadow = 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
		.Shadow = 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
	Sheets("temp_subweight").DrawingObjects("Chart 2").Shadow = 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
		.Shadow = 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
		.AddIndent = False
		.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
		.LeftHeader = ""
		.CenterHeader = ""
		.RightHeader = ""
		.LeftFooter = ""
		.CenterFooter = ""
		.RightFooter = ""
		.LeftMargin = Application.InchesToPoints(0.75)
		.RightMargin = Application.InchesToPoints(0.75)
		.TopMargin = Application.InchesToPoints(1)
		.BottomMargin = Application.InchesToPoints(1)
		.HeaderMargin = Application.InchesToPoints(0.5)
		.FooterMargin = Application.InchesToPoints(0.5)
		.PrintHeadings = False
		.PrintGridlines = False
		.PrintComments = xlPrintNoComments
		.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
		.Shadow = 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
	ActiveCell.FormulaR1C1 = "Received by:"
	With ActiveCell.Characters(Start:=1, Length:=12).Font
		.Name = "Arial"
		.FontStyle = "Bold"
		.Size = 10
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = 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
			.AddIndent = False
			.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