How do I scan a large table/worksheet for values?

[Omeganutz]

I'm having trouble figuring out how to scan an entire spreadsheet for a
value. I then need to return the corresponding values from row name and
column header, but one step at a time…lol.

I have a table with several rows and columns sorted ascending. Each column
can contain a value of interest. The value of interest is an arbitrary
constant (Say 12). I’m looking to find the “best†value of “12†in the table
that with a maximum corresponding row OR column value. Exp: Say I have a
table that is 10 rows by 10 columns. In the first Row are my column headers:
A, B, C, D, E, F, G, H, I, J. In my First Column are my row names: 50, 55,
60, 65, 70, 75, 80, 85, 90, 95. In the body of the table are values that can
range from 10 to 15. I want to find the Value “12†from the body of the
table that has the highest corresponding row name value. Simple, no? lol.

 

[Toppers]

Try something like thss:

Dim maxrow As Long, maxcol As Integer
With Worksheets(1).Range("a1:j11")
maxrow = 0
maxcol = 0
srchval = 12
Set c = .Find(srchval, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Row > maxrow Then maxrow = c.Row
If c.Column > maxcol Then maxcol = c.Column
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
MsgBox "row = " & maxrow & " column = " & maxcol


HTH