As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Another Excel Thread (Now with Visual Basic!)

CrashtardCrashtard Registered User regular
edited June 2009 in Help / Advice Forum
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.

I pinky swear that we will not screw you.

Crashtard.jpg
Crashtard on

Posts

  • OrogogusOrogogus San DiegoRegistered User regular
    edited June 2009
    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))

    Orogogus on
  • SpongeCakeSpongeCake Registered User regular
    edited June 2009
    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.

    SpongeCake on
  • CrashtardCrashtard Registered User regular
    edited June 2009
    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 on
    I pinky swear that we will not screw you.

    Crashtard.jpg
  • CrashtardCrashtard Registered User regular
    edited June 2009
    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 on
    I pinky swear that we will not screw you.

    Crashtard.jpg
  • CrashtardCrashtard Registered User regular
    edited June 2009
    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 on
    I pinky swear that we will not screw you.

    Crashtard.jpg
  • CrashtardCrashtard Registered User regular
    edited June 2009
    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

    Crashtard on
    I pinky swear that we will not screw you.

    Crashtard.jpg
  • OrogogusOrogogus San DiegoRegistered User regular
    edited June 2009
    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.

    Orogogus on
  • CrashtardCrashtard Registered User regular
    edited June 2009
    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.

    Crashtard on
    I pinky swear that we will not screw you.

    Crashtard.jpg
  • OrogogusOrogogus San DiegoRegistered User regular
    edited June 2009
    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.

    Orogogus on
  • CrashtardCrashtard Registered User regular
    edited June 2009
    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.

    Crashtard on
    I pinky swear that we will not screw you.

    Crashtard.jpg
  • Inquisitor77Inquisitor77 2 x Penny Arcade Fight Club Champion A fixed point in space and timeRegistered User regular
    edited June 2009
    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.

    Inquisitor77 on
  • OrogogusOrogogus San DiegoRegistered User regular
    edited June 2009
    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.

    Orogogus on
Sign In or Register to comment.