Macros Help - How to show Values from data pulled?

[Anthonym1154]

Good morning all,
I am working on a macro for a spreadsheet that has 20k different rows. To make it easier, I am trying to see if I can build a macro for it, but this is not my strong suit. My end goal is that if I input values to look up, and it finds a matching value, I want to pull the data for the cell directly to the right of the matching cell? Is this possible? Right now, it pulls a link that takes me to the cell, but I have to keep flipping through tabs. This is what I currentyl have:

Option Explicit

Sub ListHits()
Dim Cell2Find As Range
Dim FoundCell As Range
Dim FirstFound As Range
Dim Ct As Long
Dim KeepLooking As Boolean
For Each Cell2Find In Worksheets("Keywords").UsedRange
If Len(Cell2Find.Value) > 0 Then
Set FirstFound = Nothing
With Worksheets("Data")
Ct = 0
Set FoundCell = Nothing
Set FoundCell = .UsedRange.Find(What:=Cell2Find.Value, After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If FoundCell Is Nothing Then
KeepLooking = False
Else
Set FirstFound = FoundCell
KeepLooking = True
End If
Do While KeepLooking
Ct = Ct + 1
Cell2Find.Offset(, Ct).Hyperlinks.Add Cell2Find.Offset(, Ct), "", FoundCell.Address(external:=True), FoundCell.Address(external:=True)
'Set FoundCell = Nothing
Set FoundCell = .UsedRange.FindNext(FoundCell)
If FoundCell Is Nothing Then KeepLooking = False
If FoundCell.Address = FirstFound.Address Then KeepLooking = False
Loop
End With
End If
Next
End Sub

I appreciate any help!

 

[RobP]

Hi,

if you change this line

 Cell2Find.Offset(, Ct).Hyperlinks.Add Cell2Find.Offset(, Ct), "", FoundCell.Address(external:=True), FoundCell.Address(external:=True)

To

Cell2Find.Offset(, Ct).Value = FoundCell

Does it do what you need ? (this adds the data to the next column in "Keywords")

Rob

 

[RobP]

sorry, not quite what I had in mind.. its not functioning as expected, so I'll test some more

 

[RobP]

Cell2Find.Offset(, Ct).Value = FoundCell.Offset(0, 1)

I missed the offset. This will add the data from the next column on sheet "Data" to the sheet "Keyword" alongside the original keyword.

 

[Anthonym1154]

This is per

Cell2Find.Offset(, Ct).Value = FoundCell.Offset(0, 1)

I missed the offset. This will add the data from the next column on sheet "Data" to the sheet "Keyword" alongside the original keyword.

This is exactly what I was looking for. Thank you!

 

[RobP]

Great, thanks for the feedback.

Rob