Find Maximum Value in Column on Another Worksheet

  • Good afternoon all,

    Oh, this simple code is doing my head in!
    I can find HEAPS and HEAPS of examples to get the maximum value in a column, but for the life of me can not seem to rejig the code to look at another sheet other than the one I am working in.

    Here is what I have that works so far:

    Dim lMax As Long
        lMax = WorksheetFunction.Max(Range("A:A"))
    MsgBox lMax + 1

    What I am after is to look at the maximum value from a sheet called FOI Records and show me the value + 1

    I have toyed with many ways I know of to point this simple code's range to the sheet I want, but AGHHHH I just can't nab it.

    Can anyone help .. please?

    Thanks :)

  • Re: Find Maximum Value in Column on Another Worksheet

    Thanks .. that help has now determined why I couldn't get my code to work.

    When I add data to the row from the UserForm, it stores the FOI Number as text, so wasn't reading as a number.
    I've change the cells to numbers and it works, but I can not longer use the FOI Number to recall the row into the Userform.

    I'll need to find a way around getting the userform to store the FOI Number as a number and be able to ready it back as a number.

    Thanks guys :)

    Solved, but new error discovered. HAHA

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!