Need a VB Code to find the max number in a list

Hi,

I was trying to get a VB code for the attached sheet. Let me tell in detail what exactly im looking for.

In Sheet1 we have Group and FCode with a few groups and their relative Fcode

In Sheet2, we have 3 columns one with status, group and TCode. what im trying to get is, if im trying to insert a row with one of the group, then the Tcode should be the next number in that group.

For example, in Group Cad when i insert the row the Tcode should be 30008, how we arrive to this number is the FCode of that group multiplied by 100 plus 1 is for the first item in the group... that means the first item in group "Cad" would be (FCode*100)+1 which is (300*100)+1 = 30001. So if i add a next item in group "Cad" then the TCode would be 30002.

Hope this explains what i need. Any help is appreciated.

Thanks,
Dawson

I'm a bit confused - are you looking for code that will give you the max number in a range, or code that will allow you to calculate your "Tcode's"?

A code that will allow me to calculate the Tcode, the catch is that the Tcode should be the next number in the list of that group

Hi Dawson,

I really don't understand your question. Could you explain in another way?

I know its a bit confusing but let me try once again....
In sheet 2 any new line inserted with any group lets take for example group "Cad", then the code should automatically give me the next Tcode, in the sheet attached ideally the next Tcode would be 30008. hope this explains

Unfortunately I can't help you. Maybe some other colleague can offer a solution.