Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I scan a large table/worksheet for values?
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). Im 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I scan a large table/worksheet for values?
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 "Omeganutz" wrote: 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). Im 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a table within a large worksheet | Excel Discussion (Misc queries) | |||
How do I scan in a worksheet so that I can edit or add data? | Excel Worksheet Functions | |||
Scan range of rows and export to 2nd worksheet | Excel Programming | |||
Please help me to solve auto-scan of worksheet | Excel Programming | |||
scan for repeating values and then delete | Excel Programming |