Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

largest number in a column

Status
Not open for further replies.

kragster

Technical User
May 9, 2007
55
DK
Hi,
Could anybody help me out, or point me in the right direction. I need a macro for a button, that checks a column for the highest number and then inserts the next consequtive number into the selected cell.
 
You will need to work with a RANGE object that points to the range of cells you want to test. You will then use the MAX function to get the biggest number from that range. Put that in a variable and then add 1 to it and put the VALUE into the SELECTION that you have

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Hi. Thank you Geoff, but I am new to programming, so could you help a bit more?

This is what I have got (not much, I know):

--------------------------------
Sub NextConseqNumber()
'Declaring the variable
Dim HighestNumber As Long
'Selecting the range
Range("C10:c1000").Select
End Sub
--------------------------------
 
Code:
Sub NextConseqNumber()
'Declaring the variable
    Dim HighestNumber As Long
    Dim theRange as range
    Dim theMax
'don't select the range as you say you want to populate the selected cell   
    set theRange = Range("C10:c1000")

   theMax = application.Max(theRange)

   Selection.value = theMax+1
End Sub


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
And the one-liner version:
ActiveCell = 1 + Val(Application.Max(Range("C10:C1000")))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top