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

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" <Job@nomail.com> wrote in message
news:u20JqqkUFHA.2892@TK2MSFTNGP14.phx.gbl...
> 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
>
>
>