Find Cell with Value and get Value of Cell Below it

  • Thread starter Thread starter CribbsStyle
  • Start date Start date
C

CribbsStyle

I need to find the word "Tackles" in Column E and then have the value
of the cell below it inserted into B15 on another sheet. This seems
like it would be simple...but for some reason cant do it, lol. Any
Help?
 
this may give you an idea:

Option Explicit

Sub find_it()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim rng As Range
Dim rngfound As Range
Set ws = Worksheets("Sheet1")
Set ws1 = Worksheets("sheet2")
Set rng = ws.Columns(5)

With rng
Set rngfound = .Find("tackles", LookIn:=xlValues)
If Not rngfound Is Nothing Then
ws1.Range("B15").Value = rngfound.Offset(1)
End If
End With

End Sub
 
Name the cells with data in column E.
I used the name "LookupRange"
Place this formula in cell B15...

=INDEX(LookupRange,MATCH("Tackles",LookupRange,0)+1,1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"CribbsStyle"
wrote in message
I need to find the word "Tackles" in Column E and then have the value
of the cell below it inserted into B15 on another sheet. This seems
like it would be simple...but for some reason cant do it, lol. Any
Help?
 
Name the cells with data in column E.
I used the name "LookupRange"
Place this formula in cell B15...

=INDEX(LookupRange,MATCH("Tackles",LookupRange,0)+1,1)
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"CribbsStyle"
wrote in message
I need to find the word "Tackles" in Column E and then have the value
of the cell below it inserted into B15 on another sheet. This seems
like it would be simple...but for some reason cant do it, lol. Any
Help?
Click to expand...

Perfect! Thank You Very Much!
 
One question, lets Say I have "Tackles" in D7, D14 and D21. Is their
anyway to refer to the second instance?
 
See the response from Gary Keramidas.
Add a "FindNext" and exit on the second successful find.
The "Find" example in help will help.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"CribbsStyle"
wrote in message
One question, lets Say I have "Tackles" in D7, D14 and D21. Is their
anyway to refer to the second instance?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Move multiple values in single Cell To Cells of there own? 3
VBA set thick borders around non empty cells in range 0
Hide/Show Rows based on Cell Value with Data Validation 3
Code does not error but no copy either 4
Find a value in a sheet and replace it with another value if it matches 8
Arrays and scattered cell values or another way? 16
Insert Value into cell automatically when new rows are created/inserted 1
Dynamic Offset from an Application.Goto cell = Date or Time 10

Back
Top