#1
|
|||
|
|||
AND/OR/NOT-search
I am using an Excel-file consisting of a few thousand rows and three columns. Column three consists of text descriptions, and I want to search these cells for multiple keywords. It seems to be difficult to do this in Excel – I can only search for singular words or strings of text.
Is it possible to do an AND/OR/NOT-type of search in Excel, or do somebody have a suggestion about how I could do that in some other way? I have tried to copy the spreadsheet to MS Access but the cells contain to much text for Access. I appreciate any tip. /Johan |
#2
|
|||
|
|||
There is a good macro you can use, made by hiker95: (same macro in sample attached) You can enter in sheet Keywords how many keywords you need , the limit is the number of excel rows...
Code:
Option Explicit Option Base 1 Sub CKKeywords() Dim c As Range, a As Long, b As Long Dim MyKeys As Variant Application.ScreenUpdating = False MyKeys = Sheets("Keywords").Range("A2:A" & Sheets("Keywords").Cells(Rows.Count, 1).End(xlUp).Row) With Sheets("Sheet1") For Each c In .Range("C1", .Range("C" & Rows.Count).End(xlUp)) For a = LBound(MyKeys) To UBound(MyKeys) b = 0 On Error Resume Next b = WorksheetFunction.Find(Trim(MyKeys(a, 1)), c, 1) On Error GoTo 0 If b > 0 Then c.Offset(, 1) = "Yes" Exit For End If Next a Next c End With Application.ScreenUpdating = True End Sub |
#3
|
|||
|
|||
Hi
I am trying to run the macro but I get a “runtime error no 9: the index is outside of interval”. I’m a newb when it comes to MS Excel and programming. The problem is in the line MyKeys = Sheets("Keywords").Range("A2:A" & Sheets("Keywords").Cells(Rows.Count, 1).End(xlUp).Row) of the macro. How should I change it to conform with my spreadsheet? Thanks for your help! /Johan |
#4
|
|||
|
|||
Quote:
This part of the code assumes that your data to evaluate is in Sheet1; if not, rename worksheet as Sheet1, or type in this part of the code the name of your sheet with data; this code assumes that data range is column C; if not, change the column letter... Code:
With Sheets("Sheet1") For Each c In .Range("C1", .Range("C" & Rows.Count).End(xlUp)) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Search Function | Rick203 | Outlook | 1 | 08-29-2011 11:23 PM |
Instant Search's "Display search results as I type when possible" with Exchange | lwc | Outlook | 0 | 06-01-2011 01:56 AM |
Search for an attachment | Pryach | Outlook | 0 | 06-15-2010 09:36 AM |
Search and Replace - Clear Search box | JostClan | Word | 1 | 05-04-2010 08:46 PM |
Wildcard search help. | Kempston | Word | 0 | 11-13-2009 03:58 AM |