1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Search macro for Excel

Discussion in 'Business Applications' started by ossapmdgsd, Jan 18, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. ossapmdgsd

    ossapmdgsd Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    6
    Dear All,

    I hope this e-mail finds you well. I am trying to set up a macro for a search in excel.

    I have a column of locations I am interested in searching in column C. I then have a list of search string (villages in Nigeria) in column A. I would like a macro to choose the first search string (village name) and then search through all the locations in column C. I would like it to then report the text and row number of the cells that contain the village name in any part of the string in a separate worksheet. I then want the macro to choose the next village in column A and do the same thing over again. And so on.

    I attach a shortened version of the spreadsheet I am working with.

    I have tried searching the forum and doing this myself but I was unsuccessful. Thank you very much for your help in advance.

    With best wishes,

    Dan
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    How should the macro handle village names with special characters such as comma's, hyphens, parentheses, etc. as shown in your sample workbook?

    Rollin
     
  3. ossapmdgsd

    ossapmdgsd Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    6
    Dear Rollin,

    Thanks for getting in touch. To be honest, column A words separated by parentheses etc. would be best searched separately, but I can just separate them (through text to columns) before I start searching.

    With regards parentheses etc. in column C, it would be best if any string (e.g. between two parentheses) that fitted the village name was highlighted. I suppose I could also split column C though, so if it is an added complication, I can deal with it.

    Thank you in advance for any thoughts you have.

    With very best wishes,

    Dan
     
  4. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    This is the basic idea, unless I've completely misunderstood you.

    Code:
    Sub find_village()
    
        Sheets("cwiqvillages").Activate
        
        Dim txtVillage As Variant
        Dim eaname As Variant
        Dim getrow() As String
        Dim l As Long
        Dim ll As Long
        
        Dim nextentryrow As Variant
        Dim splitEA() As String
        
        Dim exactMatch As Boolean
        exactMatch = False
        
        
        Dim lastrow As String
        lastrow = Sheets("cwiqvillages").Range("a655366").End(xlUp).Row
        
        Dim CompareRange1 As Variant, CompareRange2 As Variant
        Set CompareRange1 = Sheets("cwiqvillages").Range("a2:a" & lastrow)
        Set CompareRange2 = Sheets("cwiqvillages").Range("c2:c" & lastrow)
        
        For Each txtVillage In CompareRange1
        
            For Each eaname In CompareRange2
                
                exactMatch = False
                
                ' Check for village name, anywhere in string, if found compare against individual words (will prevent "JOS" from catching "Joseph", for example)
                If InStr(1, eaname.Value, txtVillage.Value) Then
                    
                    ' Split the eaname to compare against the idividual words
                    splitEA = Split(eaname.Value, " ")
                    For ll = LBound(splitEA) To UBound(splitEA)
                        If splitEA(ll) = Trim(txtVillage.Value) Then
                            exactMatch = True
                        End If
                    Next ll
                    
                    If exactMatch = True Then
                        ' If the village exactly matches one of the words in eaname, fill in the results
                        getrow = Split(eaname.Address, "$")
                        l = UBound(getrow)
                        
                        ' Find next availabe row for entry
                        nextentryrow = Sheets("Sheet2").Range("a655366").End(xlUp).Row + 1
                        
                        ' Fill in results on Sheet2
                        Sheets("Sheet2").Range("a" & nextentryrow).Value = txtVillage.Value
                        Sheets("Sheet2").Range("b" & nextentryrow).Value = eaname.Value
                        Sheets("Sheet2").Range("c" & nextentryrow).Value = getrow(l)
                    
                    End If
                    
               End If
            Next eaname
         Next txtVillage
        
    End Sub
    
    This will insert the search results into Sheet2. I've attached a revised copy of your workbook, with the macro in place, so that you can see the results. Start the macro by clicking the "Run Macro" button on the first page. I can help tailor it to better suit your needs, but I don't want to put anymore time in until I'm sure I'm headed in the right direction.
     

    Attached Files:

  5. ossapmdgsd

    ossapmdgsd Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    6
    Dear gyclone,

    Wow! Thank you. Your program is exactly what I was looking for.

    My only question is that when I first ran it, it would only search down column C as far as the last non-empty row of column A. This is obviously no problem for small lists: I just filled in the blanks of row A to the last non-empty row of column C with arbitrary letters. However, I may now try a much larger set of lists. Is it possible to search column C for rows in which column A is empty?

    Thank you again for all your help.

    Dan
     
  6. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    Yes, absolutely, and that is how it should be working, now, I just made an error due to being tired. I'll post the corrected code in a few minutes.
     
  7. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    Here is the revised code:

    Code:
    Sub find_village()
    
        Sheets("cwiqvillages").Activate
        
        Dim txtVillage As Variant
        Dim eaname As Variant
        Dim getrow() As String
        Dim l As Long
        Dim ll As Long
        
        Dim nextentryrow As Variant
        Dim splitEA() As String
        
        Dim exactMatch As Boolean
        exactMatch = False
        
        
        Dim lastrowA As String
        lastrowA = Sheets("cwiqvillages").Range("a655366").End(xlUp).Row
        
        
        Dim lastrowC As String
        lastrowC = Sheets("cwiqvillages").Range("C655366").End(xlUp).Row
        
        Dim CompareRange1 As Variant, CompareRange2 As Variant
        Set CompareRange1 = Sheets("cwiqvillages").Range("a2:a" & lastrowA)
        Set CompareRange2 = Sheets("cwiqvillages").Range("c2:c" & lastrowC)
        
        For Each txtVillage In CompareRange1
        
            For Each eaname In CompareRange2
                
                exactMatch = False
                
                ' Check for village name, anywhere in string, if found compare against individual words (will prevent "JOS" from catching "Joseph", for example)
                If InStr(1, eaname.Value, txtVillage.Value) Then
                    
                    ' Split the eaname to compare against the idividual words
                    splitEA = Split(eaname.Value, " ")
                    For ll = LBound(splitEA) To UBound(splitEA)
                        If splitEA(ll) = Trim(txtVillage.Value) Then
                            exactMatch = True
                        End If
                    Next ll
                    
                    If exactMatch = True Then
                        ' If the village exactly matches one of the words in eaname, fill in the results
                        getrow = Split(eaname.Address, "$")
                        l = UBound(getrow)
                        
                        ' Find next availabe row for entry
                        nextentryrow = Sheets("Sheet2").Range("a655366").End(xlUp).Row + 1
                        
                        ' Fill in results on Sheet2
                        Sheets("Sheet2").Range("a" & nextentryrow).Value = txtVillage.Value
                        Sheets("Sheet2").Range("b" & nextentryrow).Value = eaname.Value
                        Sheets("Sheet2").Range("c" & nextentryrow).Value = getrow(l)
                    
                    End If
                    
               End If
            Next eaname
         Next txtVillage
        
    End Sub
    
    
    Just replace the entire previous macro with this one. You may have noticed that these versions don't break up any of the values in Column A that have hyphens or slashes. I can make it do that, easily, but, in order for that to work, without getting really overly complicated, the code needs to be able to treat all hyphens or all slashes, etc., the same. In other words, a hyphen or dash can't be used two separate to villages in one cell and to combine villages in another cell. Example:

    If you have a single village named "aaa-bbb", then we can't tell the program to break apart every cell when it sees a hyphen, or the code will never look for village "aaa-bbb", it will only look for villages "aaa" and "bbb".

    So before, we start splitting everything apart, we need to make sure your naming conventions are consistent, so that we can either split apart the values every time we see a particular symbol or keep the words together every time we see that particular symbol. If you are sure your conventions are consistent, just let me know which characters/symbols you want the program to treat as village dividers and I can add that functionality.
     
  8. ossapmdgsd

    ossapmdgsd Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    6
    Dear gyclone,

    You are a legend! This works really nicely. For now, I think I should define the village names appropriately, rather than have the macro sort through them. However, if I need the help, I'll definitely post again.

    Thank you so much again. I really do appreciate all your efforts.

    With best wishes,

    Dan
     
  9. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    Dan,

    You are very welcome! I think you're still going to need some tweaking to handle slashes and hyphens in column C, regardless of whether or not you split column A manually. From looking at the column C data, I'm guessing that can't be broken up manually and as it sits now, the comparison that takes place looks at the complete value in column A versus the complete value between any two spaces, or between the start or end of the cell and the nearest space, so some cells may be skipped that shouldn't (I tried to find a good example but couldn't, just keep a close eye on the behavior regarding any cells in Column C that have hyphens or slashes). It wouldn't take me too long to fix that up a bit, but I will be swamped for several hours. I'll take a peek at it later.

    Also, note that each time you run the macro it overwrites the previous results, so if you need to keep historical results, be sure to save a copy each time you run it. The macro also relies on the sheet names and column name and header locations remaining what and where they are.

    Glad I could help!

    Sean
     
  10. ossapmdgsd

    ossapmdgsd Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    6
    Dear Sean,

    OK, I understand now. That would be great. I am currently putting together a list of millions of place names, so manually checking those would be quite a bother!

    I look forward to hearing from you.
    With best wishes,

    Dan
     
  11. ossapmdgsd

    ossapmdgsd Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    6
    Dear Sean,

    I hope you are doing great. If you ever got a chance to tweak the code you sent me so to better handle column C names, I'd love to see it. NO problem if not. Just thought I'd ask.

    Very best wishes,

    Dan
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    These two lines will error out in versions 2003 and prior...
    Code:
        Dim lastrowA As String
        lastrowA = Sheets("cwiqvillages").Range("a655366").End(xlUp).Row
        
        
        Dim lastrowC As String
        lastrowC = Sheets("cwiqvillages").Range("C655366").End(xlUp).Row
    I realize there is one too many digits there (was going for 65536). But, there is a more dynamic way of doing it as well, which will work in all versions regardless...
    Code:
        Dim lastrowA As String
        Dim lastrowC As String
        With Sheets("cwiqvillages")
            lastrowA = .Cells(.Rows.Count, "A").End(xlUp).Row
            lastrowC = .Cells(.Rows.Count, "C").End(xlUp).Row
        End With
    HTH
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Search macro
  1. atrue502
    Replies:
    3
    Views:
    241
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975428

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice