Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find value in specific row
I have a VBA search function that uses 5 criteria, one of the results
is, that it tells me what row in the sheet the item is located. If I now the row the item is on, how can I use this row number to find the value in a column? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find value in specific row
The best answer will come after you supply the NG
Your existing code;;; "damorrison" wrote in message oups.com: I have a VBA search function that uses 5 criteria, one of the results is, that it tells me what row in the sheet the item is located. If I now the row the item is on, how can I use this row number to find the value in a column? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
find value in specific row
Hi D,
Dim rw As Long Dim myVal As Variant Const myCol as String = "D" '<<==== CHANGE rw = 'your known row number MyVal = Cells(rw, myCol).Value --- Regards, Norman "damorrison" wrote in message oups.com... I have a VBA search function that uses 5 criteria, one of the results is, that it tells me what row in the sheet the item is located. If I now the row the item is on, how can I use this row number to find the value in a column? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
find value in specific row
Oh! OKAY,
I was thinking of a worksheet function but if we could get this into the FindMatch function that would be great Function FindMatch(Rises As Integer, Rise As Single, Run As Single, _ OSM As Single, RoutesCuts As String) As Variant 'Looks on sheet 1 for record that has number of rises, rise, etc. respectively, in 'columns C:G. If number of rises not found, looks for next higher number. 'This is an array function that yields in successive row elements ' 1. "Exact Match", "Approx. Match", "No Match" ' 2. Number of rises found (0 if no match) ' 3. Row number in which match was found (0 if no match) Dim A(1 To 3, 1 To 1) As Variant Dim Temp As Variant Dim LastRow As Long Dim iRow As Long Dim mRow As Long 'the nearest match row found so far Dim mRises As Integer 'the smallest number of rises found so far mRises = 999 A(1, 1) = "No Match" A(2, 1) = 0 A(3, 1) = 0 With Worksheets("Sheet1") LastRow = .[C65536].End(xlUp).Row For iRow = 2 To LastRow 'Check criteria right to left If .Cells(iRow, "G") = RoutesCuts Then If .Cells(iRow, "F") = OSM Then If .Cells(iRow, "E") = Run Then If .Cells(iRow, "D") = Rise Then If .Cells(iRow, "C") = Rises Then If .Cells(iRow, "C") = Rises Then A(1, 1) = "Exact Match" A(2, 1) = Rises A(3, 1) = iRow GoTo Done Else If .Cells(iRow, "C") < mRises Then mRises = .Cells(iRow, "C") mRow = iRow End If End If End If End If End If End If End If Next iRow If mRow = 0 Then mRises = 0 GoTo Done End If A(1, 1) = "Approx. Match" A(2, 1) = mRises A(3, 1) = mRow End With Done: Temp = A FindMatch = Temp End Function |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
find value in specific row
The Index worksheet function might also work
=Index(A1:Z100,5,4) will return the contents of row 5, column 4 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
find value in specific row
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find specific worksheet in another workbook and then applu Vlookup | Excel Worksheet Functions | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
how can i find a specific date of a database | Excel Discussion (Misc queries) | |||
How can I find and format specific cells automatically in Excel? | Excel Worksheet Functions | |||
Find within Workbook. | Excel Discussion (Misc queries) |