Find Largest Value in column plus one.

[mobman]

This one is probably super easy for you guys...need to figure out how to set a variable to the largest value in a column plus one...example:

C1 = 5
C2 = 3
C3 = 4
C4 = 1

New value would be 6.


Dim NewValue as Long

NewValue = (insert formula here) + 1


Thank you for your help & time!

 

[MrKowz]

Try:

NewValue = Application.Max(Range("C:C")) + 1

 

[JackDanIce]

Try:

Sub IncrementMax ()

Dim NewValue as Long

NewValue = Application.Max(Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)) + 1
MsgBox NewValue

End Sub

 

[mobman]

Thanks for taking the time...here is one more revision to the requirements:

there are two different data sets example:


Item No Dataset
1 A
120 B
5 A
9 A
119 B

The higher item numbers correspond to Dataset B and the lower item numbers to Dataset A.

How would I code this in vba to look at the two columns, figure out the two datasets then add the appropriate next number plus 1. For example (with the data above).

If new item added is in dataset A then next number is 10
If new item added is in dataset B then next number is 121.