none
Excel VBA code to search columns and move specific text RRS feed

  • Question

  • I am looking for a VBA code to search column H (starting at H5 and going down) and find anything with "(Last Login Date MM/DD/YY)", where the date will be different from each time it finds one. I want this information then cut and pasted in column W. Nothing I have tried has worked as expected, any help is really appreciated.

    Example:

    Cell H5 contains "Account (Last Login Date 01/22/207)"

    I want it to:

    Cell H5: Account

    Cell W5: (Last Login Date 01/22/207)

    *Note, not every row has this action to take. It comes up every 20-30 rows.

    The Sheet is called "IndividualAccess"



    Wednesday, March 15, 2017 6:09 PM

Answers

  • Sub SplitDateToW()
        Dim rngC As Range
        Dim rngF As Range
        
        With Worksheets("IndividualAccess")
        
            Set rngC = Intersect(.Range("H5:H" & .Rows.Count), .UsedRange)
            
            Set rngF = rngC.Find(What:="(Last Login", After:=rngC.Cells(1), LookIn:=xlFormulas, LookAt:=xlPart)
            
            While Not rngF Is Nothing
                .Cells(rngF.Row, "W").Value = Mid(rngF.Value, InStr(rngF.Value, "(Last Login"))
                rngF.Value = Left(rngF.Value, InStr(rngF.Value, "(Last Login") - 1)
                Set rngF = rngC.FindNext(After:=rngF)
            Wend
            
        End With
    End Sub

    Wednesday, March 15, 2017 7:45 PM

All replies

  • Sub SplitDateToW()
        Dim rngC As Range
        Dim rngF As Range
        
        With Worksheets("IndividualAccess")
        
            Set rngC = Intersect(.Range("H5:H" & .Rows.Count), .UsedRange)
            
            Set rngF = rngC.Find(What:="(Last Login", After:=rngC.Cells(1), LookIn:=xlFormulas, LookAt:=xlPart)
            
            While Not rngF Is Nothing
                .Cells(rngF.Row, "W").Value = Mid(rngF.Value, InStr(rngF.Value, "(Last Login"))
                rngF.Value = Left(rngF.Value, InStr(rngF.Value, "(Last Login") - 1)
                Set rngF = rngC.FindNext(After:=rngF)
            Wend
            
        End With
    End Sub

    Wednesday, March 15, 2017 7:45 PM
  • Hi TV_TIME,

    Thanks for visiting our forum.

    Then this is the forum to discuss general questions and feedback for Microsoft Excel. Since your issue is about VBA code,  I'll move your question to the following MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Your understanding is appreciated.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Thursday, March 16, 2017 9:38 AM
  • You killed it! thank you so much!!
    Thursday, March 16, 2017 12:15 PM