Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Search!!!! Help
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!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Search!!!! Help
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 On 20 Mai, 02:40, James8309 wrote: 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!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions (search within search result) reply to this please | Excel Worksheet Functions | |||
Search lastname + firstname (search on uppercase) | Excel Programming | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
I cant do a search on this forum. Everytime I search, it comes up with zero results | Excel Programming | |||
Create a search Field within a worksheet to search command buttons | Excel Programming |