Find Largest Number
 

I have a database in Excel that we use to generate log numbers, log summary
data, track forms, etc.

I am trying to create a method of looking for a certain number in one column
and if it exists, look at another column to find the highest number used, so
that I can create a new "addendum" for that one number (see below)

28735 0
28736 0
28737 0
28737 1
28737 2
28737 3
28737 4
28738 0
28739 0
and so on

I have created a piece of code to do this, but it doesn't work right. It
finds the number, looks at the offset location but sequences wrong and
continues the loop
(28737 0 / 28737 1/ 28737 0 / 28737 1 / 28737 2 / 28737 0 /
28737 1 28737 2 / 28737 3 / 28737 0 / and so on.

The code is as follows:

Sub TestAddendum()
Dim i as long
Dim lastrow as Long
Dim a, Row as Integer
Dim Rng as Range

LastRow = Range("A10000").End(xlup).row
a=Inputbox("Enter the EO number")
Set Rng = Coulmns(2).Find(a)
For i=1 to Last Row
For row = 0 to i +1
If Rng< 0 then
MsgBox a & " " & Rng.Offset(row,1)
End If
Next Row
Next i
End Sub

Any help would be greatly appreciated.

Thanks in advance

Find Largest Number
 

asmenut wrote:
I have a database in Excel that we use to generate log numbers, log summary
data, track forms, etc.

I am trying to create a method of looking for a certain number in one column
and if it exists, look at another column to find the highest number used, so
that I can create a new "addendum" for that one number (see below)

28735 0
28736 0
28737 0
28737 1
28737 2
28737 3
28737 4
28738 0
28739 0
and so on

I have created a piece of code to do this, but it doesn't work right. It
finds the number, looks at the offset location but sequences wrong and
continues the loop
(28737 0 / 28737 1/ 28737 0 / 28737 1 / 28737 2 / 28737 0 /
28737 1 28737 2 / 28737 3 / 28737 0 / and so on.

The code is as follows:

Sub TestAddendum()
Dim i as long
Dim lastrow as Long
Dim a, Row as Integer
Dim Rng as Range

LastRow = Range("A10000").End(xlup).row
a=Inputbox("Enter the EO number")
Set Rng = Coulmns(2).Find(a)
For i=1 to Last Row
For row = 0 to i +1
If Rng< 0 then
MsgBox a & " " & Rng.Offset(row,1)
End If
Next Row
Next i
End Sub

Any help would be greatly appreciated.

Thanks in advance

-----------------------------

Actually, you can lose the VBA altogether if you want. Put the number you're
trying to match in C1, then to find the next unused sequential number:

[ ] = COUNTIF(A1:A22,C1)

Bill

Find Largest Number
 

Unfortunately, I need the VBA. This is for an Automated Application. I
utilize Excel for the Logs (6 different ones to be exact)and forms, Word for
Documents, Outlook for routing, and soon, Powerpoint for chart/Top 5 problem
reporting for Management. Once this is complete, the Excel portion will be
redeveloped in Access (SQL Back-End) to allow for a smoother flowing
application (While the company I work for prefers Excel, I have been able to
get them to understand that Excel was never developed for Multi-User
interface. The way I have linked the Logs (Databases), a relational database
environment is more efficient and more complete (don't have to hard code the
multi-user emulation).

"Bill Martin" wrote:

asmenut wrote:
I have a database in Excel that we use to generate log numbers, log summary
data, track forms, etc.

I am trying to create a method of looking for a certain number in one column
and if it exists, look at another column to find the highest number used, so
that I can create a new "addendum" for that one number (see below)

28735 0
28736 0
28737 0
28737 1
28737 2
28737 3
28737 4
28738 0
28739 0
and so on

I have created a piece of code to do this, but it doesn't work right. It
finds the number, looks at the offset location but sequences wrong and
continues the loop
(28737 0 / 28737 1/ 28737 0 / 28737 1 / 28737 2 / 28737 0 /
28737 1 28737 2 / 28737 3 / 28737 0 / and so on.

The code is as follows:

Sub TestAddendum()
Dim i as long
Dim lastrow as Long
Dim a, Row as Integer
Dim Rng as Range

LastRow = Range("A10000").End(xlup).row
a=Inputbox("Enter the EO number")
Set Rng = Coulmns(2).Find(a)
For i=1 to Last Row
For row = 0 to i +1
If Rng< 0 then
MsgBox a & " " & Rng.Offset(row,1)
End If
Next Row
Next i
End Sub

Any help would be greatly appreciated.

Thanks in advance

-----------------------------

Actually, you can lose the VBA altogether if you want. Put the number you're
trying to match in C1, then to find the next unused sequential number:

[ ] = COUNTIF(A1:A22,C1)

Bill