VBA search macro

[Zvonzi]

Hello guys!

I'm pretty new to VBA and macros, and I need your help

Basically, I was using this code to search my whole workbook (contains 5+ sheets) for a specific text (name for example).

Public Sub FindText()'Run from standard module, like: Module1.
'Find all data on all sheets!
'Do not search the sheet the found data is copied to!
'List a message box with all the found data addresses, as well!


Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer


myText = InputBox("Unesi tekst za pretragu (ime/prezime)", "Pretraga")


If myText = "" Then Exit Sub


Worksheets("Odluke, početna").Range("A1:XX200").Delete


For Each ws In ThisWorkbook.Worksheets
With ws


Set Found = .UsedRange.Find(What:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)


If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)


Found.EntireRow.Copy _
Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)


Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If


myNext:
End With
Next ws


If Len(AddressStr) Then
MsgBox "Ime/prezime " & myText & " pronađeno na:" & vbCr & _
AddressStr, vbOKOnly, myText & ""
Else:
MsgBox "Ime/prezime " & myText & " nije pronađeno", vbExclamation
End If


End Sub

I didn't write it myself, I've just put it together using various examples I've found on this forum.

I have two questions/problems I need help with:

1. How can I copy results to, for example, 10th row in Sheet1? My code copies results to 2nd row in Sheet1.
2. Is it possible to print/show value of whole row where specific text was found in a message box, instead of copying results to Sheet1? Message box from my code just shows exact cell where searched text was found.

Thank you very much in advance!

 

[yky]

Found.EntireRow.Copy _ Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)

This code copies to the next non-blank line. It starts from A65536 and go up the column A to look for a non-blank cell then copies to the cell below. You said it copied to row 2. It did so because only row 1 had data. If row10 had data, it'd copy to row 11.

You said you wanted to copy to row 10. Is that an absolute address, meaning no matter what, the code would copy to A10? Or you want to copy to nine rows below the last non-blank row? If former, use "Destination:=Worksheets("Sheet1").Range("A10"); if latter, change "Offset(1,0)" to "Offset(9,0)".

 

[Zvonzi]

Thank you very much!

Yes, that's an absolute address - so it should always copy my search results to row 10.

I've already tried changing offset value, but that doesn't suit me. Problem is that then every search result (in case my search returns multiple hits) is copied nine rows below last non-blank row.