Announcement

Collapse
No announcement yet.

Search For Text In A Specific Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    Re: Search For Text In A Specific Column

    Try this

    Code:
    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

    New users should read the Forum Rules before posting

    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.

    Comment


    • #3
      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

      Comment


      • #4
        Re: Search For Text In A Specific Column

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

        Code:
        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

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

        Comment


        • #5
          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

          Comment


          • #6
            Re: Search For Text In A Specific Column

            Where have you placed the code?
            Reafidy

            Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

            Comment


            • #7
              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

              Comment


              • #8
                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

                New users should read the Forum Rules before posting

                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.

                Comment


                • #9
                  Re: Search For Text In A Specific Column

                  When I tried to debug it highlighted this row:

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

                  Comment


                  • #10
                    Re: Search For Text In A Specific Column

                    In that line I would change it to your sheet name

                    Code:
                    Worksheets(1)
                    to 
                    Worksheets("YrSheetName")
                    Reafidy

                    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                    Comment


                    • #11
                      Re: Search For Text In A Specific Column

                      Thank you Reafidy. Yes it works now! Thanks.
                      Is there a way to change it so that the month is picked up from a cell (ie. from another worksheet)? I have the month names sent to that cell by another macro and would like this new code to pick it up and check the col.
                      Thx
                      Thank you
                      Izabella

                      Comment


                      • #12
                        Re: Search For Text In A Specific Column

                        Originally posted by Iza
                        When I tried to debug it highlighted this row:

                        Code:
                        Set rToSearch = Worksheets(1).Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp))
                        Any clues?
                        Code:
                        With Worksheets(1)
                             Set rToSearch = .Range(.Cells(1,4),.Cells(Rows.Count).End(xlUp))
                        End With

                        Comment


                        • #13
                          Re: Search For Text In A Specific Column

                          Do you mean instead of the inputbox?

                          If so:

                          Code:
                          sFind = Worksheets("YrShtName").Range("A1").Value 'change the cell range to suit
                          Reafidy

                          Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                          Comment


                          • #14
                            Re: Search For Text In A Specific Column

                            Hello Reafidy,
                            Thank you so much for your help! It works exactly how I wanted.
                            This is a great website indeed! I'm sure I'll be back!
                            Cheers
                            Thank you
                            Izabella

                            Comment


                            • #15


                              Re: Search For Text In A Specific Column

                              Hi there,
                              I tried to call another sub from this code, but is not working too well. Basically I have swapped the notification "month has been updated" to call another sub and do with that month what ever it has to do, but they can not work together for some reason... Is it because the code is explicit? Is there a way to change it?
                              Thx
                              Thank you
                              Izabella

                              Comment

                              Working...
                              X