Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when setting chart Axis value
I'm setting the Y axis dynamically based upon the calculated max. However,
the code runs fine when I step through it, but when I run total code, it will throw an error. It has something to do with the mx1 being an integer. Any ideas? The charts update dynamically based upon which row is selected. Here is a snippet of the code with some comments where I'm getting the errors. findmaxtot With TheChart.Axes(xlValue) .MinimumScale = 0 .MaximumScale = mx1 End With Sub findmaxtot() ' Dim cnt As Integer '***if I take this out and change the function so it doesn't require an integer I still get an error On Error Resume Next cnt = Application.Count(Rows("28:28")) + 2 ''''*******This is the Error point c = ColumnLetter(cnt) If WorksheetFunction.CountA(Cells) 0 Then 'Search for any entry, by searching backwards by Rows. r = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End If mx1 = Application.Max(Range("C28:" & c & r)) mx1 = Format(mx1, "#,##0") End Sub Function ColumnLetter(ColumnNumber As Integer) As String If ColumnNumber 26 Then ' 1st character: Subtract 1 to map the characters to 0-25, ' but you don't have to remap back to 1-26 ' after the 'Int' operation since columns ' 1-26 have no prefix letter ' 2nd character: Subtract 1 to map the characters to 0-25, ' but then must remap back to 1-26 after ' the 'Mod' operation by adding 1 back in ' (included in the '65') ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _ Chr(((ColumnNumber - 1) Mod 26) + 65) Else ' Columns A-Z ColumnLetter = Chr(ColumnNumber + 64) End If End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when setting chart Axis value
Ok, I'm not thinking clearly. I believe I just need to change
cnt = Application.Count(Rows("28:28")) + 2 to cnt = Application.Count(Rows(28)) + 2 "Job" wrote in message ... I'm setting the Y axis dynamically based upon the calculated max. However, the code runs fine when I step through it, but when I run total code, it will throw an error. It has something to do with the mx1 being an integer. Any ideas? The charts update dynamically based upon which row is selected. Here is a snippet of the code with some comments where I'm getting the errors. findmaxtot With TheChart.Axes(xlValue) .MinimumScale = 0 .MaximumScale = mx1 End With Sub findmaxtot() ' Dim cnt As Integer '***if I take this out and change the function so it doesn't require an integer I still get an error On Error Resume Next cnt = Application.Count(Rows("28:28")) + 2 ''''*******This is the Error point c = ColumnLetter(cnt) If WorksheetFunction.CountA(Cells) 0 Then 'Search for any entry, by searching backwards by Rows. r = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End If mx1 = Application.Max(Range("C28:" & c & r)) mx1 = Format(mx1, "#,##0") End Sub Function ColumnLetter(ColumnNumber As Integer) As String If ColumnNumber 26 Then ' 1st character: Subtract 1 to map the characters to 0-25, ' but you don't have to remap back to 1-26 ' after the 'Int' operation since columns ' 1-26 have no prefix letter ' 2nd character: Subtract 1 to map the characters to 0-25, ' but then must remap back to 1-26 after ' the 'Mod' operation by adding 1 back in ' (included in the '65') ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _ Chr(((ColumnNumber - 1) Mod 26) + 65) Else ' Columns A-Z ColumnLetter = Chr(ColumnNumber + 64) End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting reverse order for axis in OWC 11 chart component | Charts and Charting in Excel | |||
x-axis value setting | Charts and Charting in Excel | |||
setting axis from columns without using row number for line chart | New Users to Excel | |||
Setting axis parameters for a chart in the worksheet | Charts and Charting in Excel | |||
Setting Y axis | Charts and Charting in Excel |