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
>
>
>