Another Excel Thread (Now with Visual Basic!)

Crashtard

Excel users unite! So, I've got an excel spreadsheet and this is basically what I want to do. I want to search each row for a particular word, and if it finds the word then copy the row to a new sheet. I'd prefer to create the function on my own so that I understand how to set it up and work it, but I'm having a hard time finding a decent website that can help me do so. Anyone know of the website I'm looking for? I'm pretty sure it's going to be something simple with the excel functions, I'm just not sure how to set it up properly. Halp?

Edit: Also, the text can appear in any column in the row, so it has to be able to search the entire row and not just a specific column.

Orogogus

I don't know about a website, but HLOOKUP is the function you need to search a row for a given text string. And ISERROR can be used to handle rows where the term doesn't appear.

The details can be figured out from the function reference in Excel's help menu. Honestly, for basic use of Excel functions I think the function reference makes a tutorial website unnecessary. But don't let it go online; I've never found the online help useful, ever.

Anyway, I went ahead and tried my hand at the problem, and came up with this:

= IF(ISERROR(HLOOKUP("arglebargle",Sheet1!1:1,1, FALSE)),"",Sheet1!A1))

SpongeCake

Is each word present only once in a row, or multiple times? Most Lookup functions will just take the first value they find and ignore everything else, so if it's the latter things will be a little more complex.

Crashtard

It's only present in the row one time. Looking at the file, it's actually going to be two words, so it might be something like "running boy".

Crashtard

So I'm trying to do this using the IF function, but I can't figure out how to make it search every cell and not just one. I have no programming skills, and that doesn't seem to be helping any.

Crashtard

So it turns out there is a MUCH simpler way to do what I want to do. By just using the Find All (CTRL+F) function I can find all the instances of the exact text I'm looking for, but excel won't let me select those rows from the find box and copy them to a new sheet. Does anyone know if this is possible?

Crashtard

This macro is what I have, and it does exactly what I want to do. Almost. It does exactly what it needs to do, except it stops after it find the first instance in the sheet. I want it to continue until it finds ALL instances of the search string. What am I missing?

Edit: I have NO idea how to program any code, and I found this online using google. I really need to learn how to program shit.

Sub Find()
Dim lngNextRow As Long
Dim strMyString As String
Sheets("Sheet1").Activate
strMyString = InputBox("Enter the text you wish to find")
Cells.Find(What:=strMyString, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

ActiveCell.EntireRow.Copy

Sheets("Sheet2").Select
lngNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & lngNextRow).PasteSpecial
End Sub

Orogogus

I think code is going to be overkill if you're not familiar with Excel functions. In the way I did it, you would copy a function into the target sheet, in a range of cells equal in size to the range that you're searching. (i.e., if you're searching through a 9x9 range, you would copy your function into a 9x9 range).

HLOOKUP can be told to search an entire row (setting 1:1 as the range will search all of row 1, 2:2 will search row 2, etc.). So your function would say to search all of the row and then if it finds a match, it copies the equivalent cell from Sheet 1. To do that, you need to use IF, and then also ISERROR to handle what to do if it doesn't find your text.

Crashtard

Let's talk about HLOOKUP. It appears to do what I want it to do, but I think I'm just not understanding how to make it work correctly. What I want it to do is this:
Search row 2 for the phrase "FREE ACER Upgrade". If it finds it, return an A into column A. If not, don't return anything.

HLOOKUP(FREE ACER Upgrade,table_array,2,range_lookup). What exactly am I supposed to put under table array and range lookup to make this work? And sorry, I have no clue at all what I'm doing with this and so far the internet has been unhelpful in finding the answer. Except for the visual basic code I found, which worked but would only find one row with the text and then stop.

Orogogus

Make sure you put "FREE ACER Upgrade" in quotes when referencing it in the function.

table_array will be the range of cells you're searching. You want to make this an entire row, so you would use 1:1 if referencing the first row, 2:2 for the second row, or whatever. If you're copying it into another sheet, you would use something like Sheet1!1:1.

Don't set row_index_num to 2. The table_array only includes one row, so telling it to return the value in the second row will give you a #REF error.

range_lookup determines whether you need an approximate (TRUE) or exact (FALSE) match. Also, you're supposed to sort the values first for TRUE. So use FALSE.

HLOOKUP by itself is meant to find the value you want, and then return the corresponding value in the nth row in table_array. The way I would solve your problem is to put that in an ISERROR function, and then put all of that in an IF function. If HLOOKUP can't find what you're looking for, ISERROR will be true, and the value_if_true for the IF function can handle that (I told it to return ""). If HLOOKUP does find "FREE ACER Upgrade", ISERROR will be false, which value_is_false would handle.

Crashtard

So, this is what I have: =HLOOKUP("FREE ACER Upgrade",Sheet1!1:1,24,FALSE). This returns a #N/A error even while testing it in rows that I know have that value. I'm not really sure what the problem is. I'd like to upload the file so that you can see what exactly I'm trying to do, but it's from the company I work for and I've only been there a couple months.

Inquisitor77

Just type out an example of the actual layout of the document so we can see how it looks. Such as:

A B C D
1 Jim Darkmagic Loves Chicken
2 Leeroy Jenkins Stole It



I have a feeling I know what's wrong with your function, but until we see what you're working with, everything we give you is just theoretical.

Orogogus

Yeah, as written that should at least not return #N/A if it finds the "FREE ACER Upgrade" string in row 1. I assume that you've copied and pasted the formula to other rows so that 1:1 becomes 2:2, 3:3, etc. Not sure what's causing it not to work here.

However, even if you fix that problem, you're going to get a #REF because of the 24 before the FALSE. That says to return the corresponding value in the 24th row in the range you provided. But you only gave it one row, so you'll get a #REF saying that it's out of range. The 24 should just be a 1 for your purposes.