Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Search For Text In A Specific Column

  1. #1
    Join Date
    12th March 2007
    Location
    Australia
    Posts
    8

    Search For Text In A Specific Column

    Hi guys,
    I have searched and used some of the codes I found in the similar heading, however none of them actualy does what I need. I am very new to VBA, so please do not laugh if this is very simple.... I really would like someone to help me out with this.

    I need to find a word in a column, ie. "JAN" in col D (only first found is enough, not all instances) and then display message that "January has been updated", if not found, display message "this month has not been updated". The word "Jan" will be stored in a cell, so I need the code that could pick up the contents of that cell and lookup the col D (in another sheet, ie. "MthMaster") for that word.

    Thanks
    Thank you
    Izabella

  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,447

    Re: Search For Text In A Specific Column

    Try this

    VB:
    Option Explicit 
     
    Sub Find_Month() 
        Dim sFind  As String 
        Dim rToSearch As Range 
        Dim rCl    As Range 
         
        sFind = Application.InputBox("Which month") 
        Set rToSearch = Worksheets(1).Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp)) 
         
        Set rCl = rToSearch.finf(sFind, LookIn:=xlValues) 
         
        If Not rCl Is Nothing Then 
            MsgBox sFind & "  has been updated" 
        Else: MsgBox sFind & " has not been updated" 
        End If 
         
    End Sub 
    
    
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    12th March 2007
    Location
    Australia
    Posts
    8

    Re: Search For Text In A Specific Column

    Thank you for this code, but I am getting the following error: "Run-time error '1004' - Application-define or object-defined error". Not sure how to fix this. Also, I'd prefer the code to get the content of the cell that will have the month stored. Thx
    Thank you
    Izabella

  4. #4
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    Re: Search For Text In A Specific Column

    VB:
    Set rCl = rToSearch.finf(sFind, LookIn:=xlValues) 
    
    
    Change finf to find

    VB:
    Set rCl = rToSearch.find(sFind, LookIn:=xlValues) 
    
    
    it also makes a different if your using a different version of excel.

    if your using xls 2003 the format is different and you'll have to add this

    VB:
     
     
    rtoSearch.Find(What:=sFind, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _ 
    , SearchFormat:=False).Activate 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th March 2007
    Location
    Australia
    Posts
    8

    Re: Search For Text In A Specific Column

    Thank you Zimitry,
    I have tried before changing finf to find (as this as a simple typo), but it still didn't work. The version of xl I'm using is 2002. I tried with the additional line for 2003 that you have suggested, but it still gives me the same error message.

    Thank you
    Izabella

  6. #6
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,924

    Re: Search For Text In A Specific Column

    Where have you placed the code?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    12th March 2007
    Location
    Australia
    Posts
    8

    Re: Search For Text In A Specific Column

    Hello Reafidy,
    I have placed the whole code in the one of the modules in VBA. The additional line (suggestion for 2003) was placed under the line where I had to change finf to find.
    Was this supposed to go elsewhere?
    Thank you
    Izabella

  8. #8
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,447

    Re: Search For Text In A Specific Column

    Apart from the typo, the code works fine. Do you need to change the worksheet reference?
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  9. #9
    Join Date
    12th March 2007
    Location
    Australia
    Posts
    8

    Re: Search For Text In A Specific Column

    When I tried to debug it highlighted this row:

    VB:
    Set rToSearch = Worksheets(1).Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp)) 
    
    
    Any clues?
    Thank you
    Izabella

  10. #10
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,924

    Re: Search For Text In A Specific Column

    In that line I would change it to your sheet name

    VB:
    Worksheets(1) 
    To 
    Worksheets("YrSheetName") 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Search For Files Containing Specific Text In Cell
    By lifeson99 in forum EXCEL HELP
    Replies: 5
    Last Post: January 5th, 2009, 10:30
  2. Find/Search Column Heading Text Within Column
    By f.cembrola in forum EXCEL HELP
    Replies: 1
    Last Post: June 11th, 2008, 08:22
  3. Search & Find All Specific Text In Column
    By raggyryan in forum EXCEL HELP
    Replies: 3
    Last Post: May 7th, 2008, 01:43
  4. Replies: 10
    Last Post: June 8th, 2007, 04:59
  5. Replies: 1
    Last Post: September 17th, 2005, 08:49

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno