Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 19th, 2006, 10:14 PM
Registered User
Join Date: Apr 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Keyword search

I have written a VBA code for Excel 2003 that searches a 10,000 line database and unhides the row when it finds the keyword. Only problem is that I can't figure out how to make the code look through the database once and stop.

Thanks for your help


Old April 20th, 2006, 03:59 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts


For Each Cell in Search Range
Next Cell
loop will process each and every cell in the search range once only. allowing you to run though the search range once.

If you want to stop the process after finding the first instance of the keyword the you should use either
Exit Sub
Exit Function
depending on whether you're coding in a Sub or Function, after the lines of code unhiding the row.

If niether of these comments are helpful it may be worthwhile posting the actual code here and describing the problem in a bit more detail so that we can work out what specifically is the problem.


Old April 22nd, 2006, 06:06 PM
Registered User
Join Date: Apr 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts


Thanks for your help but since I am just starting out trying learn how to program my code is a mess to say the least. Anyway what I am trying to do is write a subroutine that will search a 5-10,000 line Excel database for a set of three criteria(text). If all three criteria are present on the same row then highlight that particular row so it can be found easily. Any thoughts or suggestions of the base way to accomplish this?
Any suggestions for reading materials to start learning how to do this. Most books I have seen and purchased seem to assume that the reader already knows the basics.



Old April 23rd, 2006, 08:10 AM
Registered User
Join Date: Apr 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Here is a possibility
You use a value kMax to determine the number of loops
If you have found the values set k to kMax.
Define the value you want to check as sVar1.

Sub Test()

Application.ScreenUpdating = False

    Dim k As Double
    Dim kMax As Double
    Dim sRemark As String
    Dim sVar1 As String
    Dim sVar2 As String
    Dim sSheetName As String

sSheetname = 'your sheetname
sVar1 = 'your keyword

kMax = 10000

For k = 1 To kMax

   sVar2 = Worksheets(sSheetname).Cells(k, 1).Value

   If sVar2 = sVar1 Then

      'value is found
      'insert here any other routines you want to perform

      k = kMax


      'Value is not found

      sRemark = "NOT FOUND"

    End If

Next k

Exit Sub

Please reply if this is an option, as you did not supply any code.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search by Keyword Grafixx01 Access 10 May 31st, 2007 08:59 AM
Want to Search Several Fields for the Same Keyword Eyrehead Access 3 March 16th, 2007 11:08 AM
Multiple Keyword Search tuffour Classic ASP Databases 3 September 10th, 2004 06:12 AM
highlighting the search keyword(s) Adam H-W Classic ASP Basics 2 February 10th, 2004 09:08 AM
XSLT keyword search help adamr415 XSLT 2 November 12th, 2003 08:44 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.