Code:

Sub Button4_Click()
Sheets("Support1").Select
What = InputBox("word to search")
repl = InputBox("word to replace")
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

My macro looks like this. This searches for my word within the entire sheet. However, I only want it to search within a certain range. I've tried modifying the 'LookAt;=' value to Range("H3:H30"). I get a 'script out of range error'. Is there no way to limit the search range?

I need to search for the data in column A in one workbook in ALL of the data in another workbook and have it bring back either a LISTED or NOT LISTED in another column. I have tried a couple of functions I have found on-line, but I can't seem to get any of them to work the way i want.

Basically, I need to put a function in column C of Asset Disposal and have it take the info in Column A, search the entire workbook of Dell Lease Inventory (which is too big to attach as it is a MASSIVE workbook), and then return either "listed" or "not listed" in column C of Asset Disposal.

I tried using the function posted on this website, but I can't get it to work:

VB:

Function VLOOKAllSheets() 
     
    Dim wSheet As Worksheet 
    Dim vFound 
     
    On Error Resume Next 
     
    For Each wSheet In ActiveWorkbook.Worksheets 
        With wSheet 
            Set Tble_Array = .Range(Tble_Array.Address) 
            vFound = WorksheetFunction.VLookup _ 
            (Look_Value, Tble_Array, _ 
            Col_num, Range_look) 
        End With 
        If Not IsEmpty(vFound) Then Exit For 
    Next wSheet 
     
    Set Tble_Array = Nothing 
    VLOOKAllSheets = vFound 
End Function 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Any ideas or help will be GREATLY appreciated!!!

Thanks!

Hi... I need to Vlookup to look in the entire workbook. The data I want to return is on in the workbook once, but I need it look for it across about sheets. Is this possible? I tried doing on search on the boards and can't find anything.

Thanks!!

Hi,

I came to a piece of vba but something is wrong with it... Problem is I don't know a solution.

I have a form that searches in an entire workbook for certain keywords, for example I fill in a last name in the textfield txtName and pus the search command button. As a result I get all the rows of the entire workbook containing this name. There are othre search fields in the form that do similar things...


This is the vba code:
Code:

Private Sub cmdOk_Click()
 
 
 
 Dim i As Integer, SheetC As Integer
 
 Do

SheetC = Sheets.Count

For i = SheetC To 1 Step -1


       With ActiveSheet
            
        .AutoFilterMode = False
        .Range("A3:K150").AutoFilter

        With .AutoFilter.Range

            If txtLastName.Value <> "" Then
                .AutoFilter Field:=1, Criteria1:="*" & txtLastName.Value & "*"
            End If
            
            If txtFirstName.Value <> "" Then
                .AutoFilter Field:=2, Criteria1:="*" & txtFirstName.Value & "*"
            End If
            
            If txtStreet.Value <> "" Then
                .AutoFilter Field:=3, Criteria1:="*" & txtStreet.Value & "*"
            End If
            
            If txtPostalCode.Value <> "" Then
                .AutoFilter Field:=4, Criteria1:="*" & txtPostelCode.Value & "*"
            End If
            
            If txtCity.Value <> "" Then
                .AutoFilter Field:=5, Criteria1:="*" & txtCity.Value & "*"
            End If
            
            If txtPhoneNumber.Value <> "" Then
                .AutoFilter Field:=6, Criteria1:="*" & txtPhoneNumber.Value & "*"
            End If
            
        End With
        
         
    End With
    ActiveSheet.Next.Select
    
    Next i
Loop

End Sub

The problem is the following line: ActiveSheet.Next.Select

Does anyone have any idea about what is wrong with it? I'm rather new at VBA, so I can't figure it out (yet)

Thanks in advance

T

Hey everyone,

I am new to visual basic and I have been trying to figure something out for a while now. Pretty much, I need to create a macro in excel that will search a different excel file for anything that matches a certain criteria, copies whatever the value is in the column c, and paste it back into the original excel file. I have to do this for several items, but if I get the first item down, I think I can figure it out for the rest of them. Anyways, here is an example:

I have item number 5202001. So the macro searches the other excel file for 5202001, but this item must meet certain criteria (this criteria will be the same for every item.) The criteria is that whatever is in cell B must equal CTD or HQ. Whatever is in cell I must be equal to CTDRM.


The macro will find an item that matches this criteria, it copies the value in cell C, which is a quantity, and then pastes it back in the original excel file in cell E38.

Thanks for any help you can give me, I am so lost right now and I have been trying to figure this out on my own for about 2 weeks now.

Getting valid web searches and avoiding use of "Forums" that contaminate
Google web searches by including newsgroups postings.

Newsgroups postings can be searched using Google Groups, and such
postings should NOT show up on a typical web search. For more
information on (Excel) Newsgroups see
http://www.mvps.org/dmcritchie/excel/xlnews.htm

WEB SEARCHES:
Using a Google web search is about the usual way you can get to web
sites with Excel information. There are other search engines available
but Google is the most popular, and probably still has the most links.

The problem with web searches is the contamination from spammy sites.
Just like there are many degrees of spam the same can be said of
spammy sites, so most of what you are seeing fits under the radar
of measurements. Just like off topic posts to multiple newsgroups
is frequently off the radar of spam measurements, even though that
was the original definition of spam. Google and others will usually
catch spam directed to thousands of servers/newsgroups as you
can somewhat see in your ISPs spam folders.

But the sites I am saying that contaminate the Google web search
results are those that attract people to post questions through their site
when in fact all they are doing is passing the postings on to actual
newsgroups, and then those plus all responses end up on their
website to attract more users doing web searches.

One way to eliminate a lot of such webpages is to include negatives
on a web search -htm -site:excelforum.com -site:exceltips.com
unfortunately you would need over eighty such items that are even more
generic than those. And Google only allows you about 30 items.
Along these lines are 3 google search forms on my search.htm page.

A better way is to use Firefox and install "Customize Google" and create
your filter of sites you do not want to visit. Unfortunately for my own filter, it
is only for Excel and as you can only have one filter, so I would have to turn
off the filter for searches when I want to see forums for applications that
don't make much use of newsgroups (i.e. help with Firefox).

Sites that I filter out are those that regurgitate newsgroup postings, and sites that
are more interested in advertising than in providing free useful Excel information.
More information on my Firefox page in this section (has links, including to my filter)
Note my filtering works for me, others may want to be more specific in their choices.
http://www.mvps.org/dmcritchie/firef...ustomizegoogle

The quickest way for many would be to search only certain websites
for Excel information -- these would be fairly much weighted toward
present and past Excel MVPs which is not a bad place to start but
for specialized material for a specific vocations you could be missing
lot of really valuable information. See Ron de Bruin's site
http://www.rondebruin.nl/Google.htm for Google Search Excel Add-in

By posting your own questions directly to a newsgroup instead of
through a web site, you are not enabling such sites. Exceptions
of web pages that will not add to the problem are Google Groups and
Microsoft Communities (discussions), that specifically exclude themselves
from Google web searches.
http://www.mvps.org/dmcritchie/excel/xlnews.htm

If you are not behind a firewall you can post directly to a newsgroup
with Outlook Express, or through your browser, paste the following
into your browser's address bar (location bar)
news://msnews.microsoft.com/microsof...excel.newusers

ISPs used to help you with newsgroups, that seems to be pretty
much a thing of the past.

For tips on using Outlook Express see
http://www.mvps.org/dmcritchie/oe/oe6.htm


footnote:
This was getting much too specific about working with newsgroups so started
this post more or less as a continuation from:
http://groups.google.com/groups?thre...TNGP04.phx.gbl

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


--
---
HTH, (Hope This Helps)
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,


i'm trying to figure out if there's a way to specify how VLOOKUP searches a column.


from the excel help files i understand it looks for the lookup_value in the 1st column of the table_array. The problem i have is that the value i want to use as the lookup_value appears multiple times in the 1st column.


here's an simplified example of the spreadsheet i'm using :


http://img854.imageshack.us/img854/9365/exampler.jpg


so in cells C2, C3 and C4 i need a formula that will look up column A for the next "X" value and return the value in column B. So C2, C3 and C4 will end up showing the value "2"


In cells C10, C11 and C12 using the same formula they would show the value "4"


It needs to be the exact same formula searching the range A1:B16 cos the real spreadsheet has around 200-300 sections called "X" and i want to copy the formula down rather than having a different formula/range for each section.


there's a bit more to it after this but thought it'd be best to see if this is possible first...


thanks in advance!

Hi, how do i write a macro that will find a section of text and replace it with nothing. This has to be performed in the whole of a workbook where the workbook has 250 sheets

I have tried using the wizard (Tools > Macro > Record New Macro )to do this and selecting 'Search within Workbook' but when i re-ran the macro it only performed its find and replace in the active sheet I was when the macro was written.

Any help would be greatly appreciated!!!

Matt

I would like to ask recommendations whether to use DSum or Vlookup, in order to do multiple search.

We receive a flat file on a daily basis, which contains four (4) columns. They are Group, Team, Taskname, and Number_of_Employees. The first three columns have text properties and the last one has number property. We copy this sheet into our workbook. There are more than 1,000 rows. I name this tab, "TotalWorkersbyTask".

Each group (first column) has its own tab. We try our best to name each tab to match the name of the first column.

We would like to find the unique value, in order to put the correct Number_Of_Employees into the correct column on each sheet. However, the relationship per column is many to many, which makes it more difficult.

For example,

Group,Team, TaskName, Number_of_Employees

12GR, Robert, Analyze raw data, 2.5
12OV, Tim, Analyze raw data, 1.25
12OV, Bob, Prepare reports, .85
16CJ, Jim, Prepare reports, .50
1008, Mary, Analyze raw data, .25
............

Each tab, such as 12GR, will have the task name on the line "Analyze raw Data" which matches the information "TotalWorkersbyTask" tab.

The problem is, if I do a Vlookup for Taskname only, only the first value of 2.5 will return in to the Number_of_Employees column.

For the tab of group 12GR, the logic supposes to be -> if the group is 12GR, and the name of the task equal to the one appeared in "TotalWorkersbyTask", return me the value of 2.5 in the specific row. With the same logic, on the tab 12OV, the same task name should return me the value of 1.25 as well.

As you see from the example, line 2 and line 3 are the tasks in the same tab, from the flat file received.

Then, this formula can be copied and populated into other rows and other sheets as well.

*********************
Questions:

1. Is there anyway to write the formula to gather the required information? My thought was using the Vlookup. However, it can see only one column.

2. Do I need to rename all the tabs to match the name of the first column received from flat file in order to make it more efficient? (In the present, there are some tabs that match the name, such as 12OV, and some tabs are something like ->Engineering, Research, etc.)

I am thinking about the concatenation, in order to do this. I would like to see whether there are any better and more efficient ways to write the formula. Please assist. Thank you.