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?

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?

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?

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

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

find value in specific row
 

wrote:
The Index worksheet function might also work

=Index(A1:Z100,5,4) will return the contents of row 5, column 4

It sure does thanks, and I am still trying out the code, don't know
where I should put it though