+ Reply to Thread
Results 1 to 8 of 8

Find text in cell using cells object with-in IF Then statement

Hybrid View

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    Guernsey, C.I
    MS-Off Ver
    Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 64-bit
    Posts
    39

    Find text in cell using cells object with-in IF Then statement

    Im am trying to find particular text in a cell then format adjacent cells in the same row. In my code below I am trying to search for "*[Tx]*" using Like, however this is formatting all text that contain "T".

    Any help much appreciated.

    Sub Summary()
    
    ' for each cell in column E find [Tx] then format borders
    ' for each cell in column E find [H] adjacent cell in columns A:A,D:D,F:F,I:I .clear
    ' Find [Tx] and [H] in column E and clear this text.
    
       
        Dim i As Long
        Dim LC As Integer
        
        LC = Sh1.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
       
        Set Sh1 = Sheets("Statement of Profit & Loss")
        u1 = Sh1.Range("E" & Rows.Count).End(xlUp).Row
    
        
        
    For i = 5 To u1
        With Sh1.Cells(i, "E")
            If Sh1.Cells(i, "E") = "" Then
                Sh1.Rows(i & ":" & i).Clear
                
                 ElseIf Sh1.Cells(i, "E") Like "*[H]*" Then
                   .Offset(, -2).Clear
                   .Offset(, -4).Clear
                   .Offset(, 1).Clear
                   .Offset(, 3).Clear
                   
                   'does not work
                ElseIf Sh1.Cells(i, "E") Like "* [Tx]" Then
                    With Sh1.Rows(i & ":" & i).borders(xlEdgeTop)
                            .LineStyle = xlContinuous
                            .Weight = xlThin
                        End With
                     With Sh1.Rows(i & ":" & i).borders(xlEdgeBottom)
                             .LineStyle = xlContinuous
                              .Weight = xlMedium
                        End With
                   
            End If
       
         End With
        
    Next i
        
    End Sub

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Find text in cell using cells object with-in IF Then statement

    can you attach a sample file with data and desired result ?
    If solved remember to mark Thread as solved

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Find text in cell using cells object with-in IF Then statement

    Try this:

    Sub Summary()
    Dim ws As Worksheet:    Set ws = Sheets("Statement of Profit & Loss")
    Dim LR As Long
    Dim rCell As Range
    
    LR = ws.Range("E" & Rows.Count).End(xlUp).Row
    
    For Each rCell In ws.Range("E1:E" & LR)
        If Not InStr(1, rCell.Value, "[Tx]") = 0 Then
            With rCell.EntireRow.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
            With rCell.EntireRow.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        ElseIf Not InStr(1, rCell.Value, "[H]") = 0 Then
            rCell.Offset(, -1).Clear
            rCell.Offset(, -4).Clear
            rCell.Offset(, 1).Clear
            rCell.Offset(, 4).Clear
        ElseIf IsEmpty(rCell) Then
            rCell.EntireRow.Clear
        End If
    Next rCell
    
    End Sub

  4. #4
    Registered User
    Join Date
    05-16-2012
    Location
    Guernsey, C.I
    MS-Off Ver
    Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 64-bit
    Posts
    39

    Re: Find text in cell using cells object with-in IF Then statement

    Quote Originally Posted by stnkynts View Post
    Try this:

    Sub Summary()
    Dim ws As Worksheet:    Set ws = Sheets("Statement of Profit & Loss")
    Dim LR As Long
    Dim rCell As Range
    
    LR = ws.Range("E" & Rows.Count).End(xlUp).Row
    
    For Each rCell In ws.Range("E1:E" & LR)
        If Not InStr(1, rCell.Value, "[Tx]") = 0 Then
            With rCell.EntireRow.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
            With rCell.EntireRow.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        ElseIf Not InStr(1, rCell.Value, "[H]") = 0 Then
            rCell.Offset(, -1).Clear
            rCell.Offset(, -4).Clear
            rCell.Offset(, 1).Clear
            rCell.Offset(, 4).Clear
        ElseIf IsEmpty(rCell) Then
            rCell.EntireRow.Clear
        End If
    Next rCell
    
    End Sub

    Hi stnkynts,
    Thanks for the code this works, could the last column used be include to replace EntireRow for the cell borders formatting, apologies I should have included this is my initial request?

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Find text in cell using cells object with-in IF Then statement

    Maybe this:

    Sub Summary()
    Dim ws As Worksheet:    Set ws = Sheets("Statement of Profit & Loss")
    Dim LR As Long, LC As Long
    Dim rCell As Range
    
    LR = ws.Range("E" & Rows.Count).End(xlUp).Row
    LC = ws.UsedRange.Columns.Count
    
    For Each rCell In ws.Range("E1:E" & LR)
        If Not InStr(1, rCell.Value, "[Tx]") = 0 Then
            With rCell.Resize(1, LC - rCell.Column).Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
            With rCell.Resize(1, LC - rCell.Column).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        ElseIf Not InStr(1, rCell.Value, "[H]") = 0 Then
            rCell.Offset(, -1).Clear
            rCell.Offset(, -4).Clear
            rCell.Offset(, 1).Clear
            rCell.Offset(, 4).Clear
        ElseIf IsEmpty(rCell) Then
            rCell.Resize(1, LC - rCell.Column).Clear
        End If
    Next rCell
    
    End Sub

  6. #6
    Registered User
    Join Date
    05-16-2012
    Location
    Guernsey, C.I
    MS-Off Ver
    Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 64-bit
    Posts
    39

    Re: Find text in cell using cells object with-in IF Then statement

    Almost there, I have attached a workbook showing a before and after worksheet. The before worksheet "Statement of Profit & Loss" is prior to any macro being run the "Statement of Profit & Loss NEW" is what I want the worksheet to look like.
    Attached Files Attached Files
    Last edited by DevfromCorrie; 05-06-2014 at 02:28 PM. Reason: Wrong file loaded

  7. #7
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Find text in cell using cells object with-in IF Then statement

    This would have been much easier had we had the workbook in the first place.

    Sub Summary()
    Dim ws As Worksheet:    Set ws = Sheets("Statement of Profit & Loss")
    Dim LR As Long, LC As Long
    Dim rCell As Range
    
    LR = ws.Range("E" & Rows.Count).End(xlUp).Row
    LC = ws.UsedRange.Columns.Count
    
    Application.ScreenUpdating = False
    
    For Each rCell In ws.Range("E5:E" & LR)
        If Not InStr(1, rCell.Value, "[Tx]") = 0 Then
            With rCell.Offset(0, -4).Resize(1, 4)
                .borders(xlEdgeTop).LineStyle = xlContinuous
                .borders(xlEdgeTop).Weight = xlThin
                .borders(xlEdgeBottom).LineStyle = xlContinuous
                .borders(xlEdgeBottom).Weight = xlMedium
            End With
            With rCell.Offset(0, 1).Resize(1, LC - rCell.Column)
                .borders(xlEdgeTop).LineStyle = xlContinuous
                .borders(xlEdgeTop).Weight = xlThin
                .borders(xlEdgeBottom).LineStyle = xlContinuous
                .borders(xlEdgeBottom).Weight = xlMedium
            End With
        ElseIf Not InStr(1, rCell.Value, "[H]") = 0 Then
            rCell.Offset(, -1).ClearContents
            rCell.Offset(, -4).ClearContents
            rCell.Offset(, 1).ClearContents
            rCell.Offset(, 4).ClearContents
        ElseIf IsEmpty(rCell) Then
            rCell.Offset(0, -4).Resize(1, 4).ClearContents
            rCell.Offset(0, 1).Resize(1, LC - rCell.Column).ClearContents
        End If
    Next rCell
    
    Application.ScreenUpdating = True
    
    End Sub

  8. #8
    Registered User
    Join Date
    05-16-2012
    Location
    Guernsey, C.I
    MS-Off Ver
    Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 64-bit
    Posts
    39

    Re: Find text in cell using cells object with-in IF Then statement

    Many thanks this is exactly what im after.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find some cell's text in another column and get the text of those cells
    By simone77 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-25-2013, 11:39 AM
  2. find and list cells based on text on its next cell
    By calif in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-13-2013, 04:24 AM
  3. Formula to Find If Text In a Cell Matches Another Range of Cells
    By purplesamcat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2013, 07:45 PM
  4. Problems with Cells.Find. Object/Block variable not set
    By mattisch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2007, 09:06 AM
  5. find text in a cell and make cell 2 cells below that active
    By shark102 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-20-2005, 10:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1