VBA, Search!!!! Help

[James8309]

Hi everyone,

In my workbook, I have 70 worksheets. Worksheet names are in month +
year.


i.e. Jan 05, Feb 05... and so on.


Problem I am having with my worksheets is that they all contain data
from 'text' file. This is because I got the output from billing
system. They have different number of columns rows and even have some
weird square box like symbols. All the data is in one column as well.
Very messy.
Obviously I can't run any look ups in this worksheet. I can however
search a single sheet using "Ctrl + F". I just know how to program in
VBA like this;


Range("G20").Select
Cells.Find(What:="EP0011", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate


**
I was wondering if my last or current sheet, from column A1 to A50
contains data that I need to search on all the worksheets.
1. How do I make it search all the values from A1 to A50 on all
worksheets? if value is found in certain worksheet, is it possible to
make it return the name of the worksheet in the next column B1 to
B50.


2. How do use 'next' function to automate all this process?


than you so much guys!!!!

 

[steonken]

hi James,

try this code. the sheet where A1-A50 contains the data to search must
be active, but this can be changed in the code.

Sub SearchAllSheets()
For i = 1 To 50
If Range("A" & i) <> "" Then
Range("B" & i).ClearContents
For Each sh In Worksheets
If sh.Name <> ActiveSheet.Name Then
Set searchresult = sh.Cells.Find(Range("A" & i), _
lookat:=xlPart)
If Not searchresult Is Nothing Then
Range("B" & i) = Range("B" & i) & " " & sh.Name
End If
End If
Next
End If
Next
End Sub

bye
stefan