none
Find text in column RRS feed

  • Question

  • Hi, this has to be a simple code, but I'm just can't think of it. 

    All i'm trying to do is find hte text in a column. If the text is found then proceed with a call. My code is debugging Where it says 

    "Set Findtext =Selection..etc"

    Any suggestions??? Thanks in advance!

    Dim wsmacro As Worksheet
    Set wsmacro = Worksheets("Macro")

    wsmacro.Activate
          
    With ActiveSheet
    Dim Findtext As Range
    'lrow = Range("b" & Rows.Count).End(xlUp).Row
    Set Findtext = Range("B3:B40")
    n = Worksheets("Macro").Range("B3:0").Cells.SpecialCells(xlCellTypeConstants).Count

    'Columns("B:B").Select
    '    Selection.Find(What:="Atlantic City", After:=ActiveCell, LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Activate
            
    Set Findtext = Selection.Find(What:="Atlantic City", After:=ActiveCell, LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Activate
    'Cells.Find(What:="Atlantic City", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
                                                                                xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                                                                                , SearchFormat:=False)
    If n Is Nothing Then
    Call baltimore
    Else
    Call atlantic
    End If
    End With
    End Sub

    Tuesday, February 21, 2017 12:22 AM

Answers

  • Hi,

    Remove .Activae when using Find method and it should be "If Findtext Is Nothing"

    E.g.

    Set Findtext = Selection.Find(What:="test", After:=ActiveCell, LookIn:= _
     xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)
    'Cells.Find(What:="Atlantic City", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
     xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
     , SearchFormat:=False)
    If Findtext Is Nothing Then

    Besides, line n = Worksheets("Macro").Range("B3:0").Cells.SpecialCells(xlCellTypeConstants).Count
    The range is wrong reference and there is no need to declare the variable when using Find.  

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Legzen34 Wednesday, February 22, 2017 11:39 PM
    Tuesday, February 21, 2017 3:04 AM
    Moderator

All replies

  • Hi,

    Remove .Activae when using Find method and it should be "If Findtext Is Nothing"

    E.g.

    Set Findtext = Selection.Find(What:="test", After:=ActiveCell, LookIn:= _
     xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)
    'Cells.Find(What:="Atlantic City", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
     xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
     , SearchFormat:=False)
    If Findtext Is Nothing Then

    Besides, line n = Worksheets("Macro").Range("B3:0").Cells.SpecialCells(xlCellTypeConstants).Count
    The range is wrong reference and there is no need to declare the variable when using Find.  

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Legzen34 Wednesday, February 22, 2017 11:39 PM
    Tuesday, February 21, 2017 3:04 AM
    Moderator
  • I think i was tired..LOL

    That worked. I removed the activate and it worked just fine...

    Thanks!

    Wednesday, February 22, 2017 11:39 PM