Help with find function to search records and copy associated hyperlinks

[ayriksun]

I am new to the forums, and really new to VBA in excel. I have found some code to search for a text string, and I tried to adapt it to search through sheets in a workbook for a string "Logs" and then copy the cell 2 columns to the right. Then I am trying to paste the hyperlink at the end of the row entry on the master sheet in the workbook "6N656667". This is pretty ambitious code for me, and I'm not even proficient enough at debugging it to figure out where my mistake is. The code is as follows:

Sub lookforlas()


'For i=3 to 2420
For x = 3 To x = 103
yy = 24
'mystr is the name of the sheet we are searching for logs
Worksheets("5N656667").Select
Worksheets("5N656667").Activate
mystr = "Cells(x, 4)"





Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range


'What value do you want to find (must be in string form)?
fnd = "*Log*"


Set myRange = Worksheets(mystr).UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If


Set rng = FoundCell
Cells(FoundCell.Row, 6).Copy



Worksheets("5N656667").Cells(x, yy).PasteSpecial Paste:=xlPasteAll
yy = yy + 1


'Loop until cycled through all unique finds
Do Until FoundCell Is Nothing
'Find next cell with fnd value
Set FoundCell = myRange.FindNext(after:=FoundCell)



'Test to see if cycled through to first found cell
If FoundCell.Address = FirstFound Then Exit Do

Loop




Exit Sub


'Error Handler
NothingFound:
Worksheets("5N656667").Cells(x, 24).Value = "No Logs Found"




Next x

End Sub

 

[skywriter]

I can probably help you work through this.

What I've done is all preliminary, it's by no means meant to be final and work without a hitch.

I made some changes and a lot of comments, some with questions that you need to answer if you want more help.

You need to use code tags when posting code.

Click the # on the toolbar and then paste your code between the two code tags that pop up. and after you confirm your post your code will look like mine below.

If you get errors, I need to know which line it occurred on and what the description and error number are.

Please give details don't use terms like it doesn't work and I get an error, be specific.

Sub lookforlas()
Dim fnd As String, FirstFound As String
Dim rng As Range
Dim myRange As Range, LastCell As Range

'For i=3 to 2420
For x = 3 To 103
yy = 24
'mystr is the name of the sheet we are searching for logs
Worksheets("5N656667").Select
Worksheets("5N656667").Activate
mystr = "Cells(x, 4)"   'Do you want mystr to literally be the text "Cell(x,4)" or the value in cell(x,4) in Worksheet 5N656667?

'What value do you want to find (must be in string form)?
fnd = "*Log*"      'If Log can be part of a longer text then just use "Log" and LookAt:=xlPart


Set myRange = Worksheets(mystr).UsedRange  ' So regarding the question I asked above, you have a sheet named "Cells(x, 4)"
Set LastCell = myRange.SpecialCells(xlCellTypeLastCell)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If

Set rng = FoundCell ' why do you need another range, just use rng, see example below.
Cells(rng.Row, 6).Copy



Worksheets("5N656667").Cells(x, yy).PasteSpecial Paste:=xlPasteAll
yy = yy + 1


'Loop until cycled through all unique finds
' I changed this to rng, see comments above.
'Do Until rng Is Nothing  ' By virtue of your code above you wouldn't be here if something wasn't already found, so not needed and it will always find something.
Do                                                    ' So just remove everything except do and use code below to exit the loop.
'Find next cell with fnd value
Set rng = myRange.FindNext(after:=rng)

'Test to see if cycled through to first found cell
If rng.Address = FirstFound Then Exit Do

Loop

Exit Sub

'Error Handler
NothingFound:
Worksheets("5N656667").Cells(x, 24).Value = "No Logs Found"

Next x

End Sub

 

[ayriksun]

Bruce:

Thanks so much for the help. With your notes, I was able to get it to start giving me outputs, and I could step through the program line by line. I made a few changes and moved things around and this is what the final product looks like:

Sub lookforlas()Dim fnd As String, FirstFound As String
Dim rng As Range
Dim myRange As Range, LastCell As Range


'For i=3 to 2420
For x = 3 To 2420
yy = 24
'mystr is the name of the sheet we are searching for logs
Worksheets("5N656667").Select
Worksheets("5N656667").Activate
mystr = Cells(x, 4)   'Do you want mystr to literally be the text "Cell(x,4)" or the value in cell(x,4) in Worksheet 5N656667?


'What value do you want to find (must be in string form)?
fnd = "Well Logs"      'If Log can be part of a longer text then just use "Log" and LookAt:=xlPart




Set myRange = Worksheets(mystr).UsedRange  ' So regarding the question I asked above, you have a sheet named "Cells(x, 4)"
Set LastCell = myRange.SpecialCells(xlCellTypeLastCell)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)


'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If


Set rng = FoundCell ' why do you need another range, just use rng, see example below.
    Worksheets(mystr).Cells(rng.Row, 4).Copy
    Worksheets("5N656667").Cells(x, yy).PasteSpecial Paste:=xlPasteAll
    yy = yy + 1




'Loop until cycled through all unique finds
' I changed this to rng, see comments above.
'Do Until rng Is Nothing  ' By virtue of your code above you wouldn't be here if something wasn't already found, so not needed and it will always find something.
Do                                                    ' So just remove everything except do and use code below to exit the loop.
    
    'Find next cell with fnd value
    Set rng = myRange.FindNext(after:=rng)


'Test to see if cycled through to first found cell
If rng.Address = FirstFound Then Exit Do


Worksheets(mystr).Cells(rng.Row, 4).Copy
    Worksheets("5N656667").Cells(x, yy).PasteSpecial Paste:=xlPasteAll
    yy = yy + 1


Loop
'Error Handler
NothingFound: Next x


Exit Sub














End Sub

 

[skywriter]

My pleasure, thanks for the feedback.