"Gen" is the text you are searching for in the worksheet so for example if you wanted to search for "Hello" you would replace "Gen" with "hello":
Set RecordRow = Range("D1:D10,E1:E10").Find(What:="hello", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If RecordRow Is Nothing Then
MsgBox ("no matches")
Else
MsgBox (RecordRow.Row)
Dim Vol As String
Vol = Cells(RecordRow.Row, 5).Value
MsgBox (Vol)
End If
xlValues means you are looking at the actual values of the cells instead of the formulaes e.g.
H2 = 50
I2 = 50
L2 = H2 + I2
L2 has a formula in its cell. The formula is "H2 + I2". If you searched with
Set RecordRow = Range("D1:D10,E1:E10").Find(What:="H2+I2", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
...it would look for "=H2+I2" forumlae in the given range.
For example if L2 's value is 100 and it got from H2 + I2 where H2 is 50 and I2 is 50.
Then the formula of L2 is H2+I2, the value of L2 is 100.
What you want to do is search all the data on the worksheet and whenever it finds a match - it should save the row of the matched cell into a variable and use that value to extract all the rows data in each column.
My previous example showed how to extract the volume data from the worksheet after finding a match to a search term.
What you want to do is somehting like this:
Dim SearchTerm as String
SearchTerm ="gen"
'Change the searchterm ot whatever you want.
'Example: SearchTerm = TextBox1.Text
Set RecordRow = Range("D1:D10,E1:E10").Find(What:=SearchTerm, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If RecordRow Is Nothing Then
MsgBox ("no matches")
Else
MsgBox (RecordRow.Row)
Dim Vol As String
Vol = Cells(RecordRow.Row, 5).Value
'Extract other columns data in the same manner as the volume given here.
MsgBox (Vol)
End If
Do you have further questions?