find a word in sheet

  • Thread starter Thread starter John
  • Start date Start date
J

John

Dear all,

I want to use VBA to look for a word (for example "AAA") in one sheet.
but I want to find this word sheet by sheet and then copy the this word (if
find) to another sheet (for example,copy this word to sheet1)

I don't know how to loop the sheet(one by one) and how to look for this
work and then copy this word to another sheet.

Thank you
 
will this macro be of help to you ?

Code: 
Sub findaaa()
 Dim j As Integer, r As Range, dest As Range
 Dim k As Integer, m As Integer
 k = Worksheets.Count
 'MsgBox k
 For m = 2 To k
 Set r = Worksheets(m).UsedRange
 j = WorksheetFunction.CountIf(r, "aaa")
 Set dest = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
 Range(dest, dest.Offset(j - 1, 0)) = "aaa"
 Next m
 End Sub
 
Sub FindThings()
Dim s As String, i As Long
s = "AAA"
i = 1

Dim w As Worksheet, results As Worksheet
Set ws = Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
Set results = Sheets("results")

Dim r As Range, rr As Range

For Each w In ws
w.Activate
Set r = ActiveSheet.UsedRange
For Each rr In r
If rr.Value = s Then
results.Cells(i, 1) = w.Name
results.Cells(i, 2) = rr.Address
results.Cells(i, 3) = s
i = i + 1
End If
Next
Next
End Sub

Here we create an array of three worksheets. We loop over this array and in
each worksheet search for AAA. When we find AAA, we record the source in a
worksheet called results. We record the name of the sheet, the address
within the sheet and the value.

This can easily be modified to look for AAA within other text.
 
Anotherway using Find & FindNext which should be quicker..

Sub Macro()

Dim varFound As Variant, varSearch As Variant, ws As Worksheet
Dim strAddress As String, intCount As Integer, intSheet As Integer

varSearch = "word"

For intSheet = 2 To Sheets.Count
Set ws = Worksheets(intSheet)
Set varFound = ws.Cells.Find(varSearch, , xlValues, xlPart)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
intCount = intCount + 1
Sheets("Sheet1").Range("A" & intCount) = ws.Name
Sheets("Sheet1").Range("B" & intCount) = strAddress
Sheets("Sheet1").Range("C" & intCount) = varFound.Text
Set varFound = ws.Cells.FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address <> strAddress
End If
Next

End Sub

If this post helps click Yes
 
Back
Top