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

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