+ Reply to Thread
Results 1 to 5 of 5

Search for string across header row, then search for another string down found column

Hybrid View

TucsonJack Search for string across... 05-09-2012, 02:21 PM
StevenM Re: Search for string across... 05-09-2012, 03:02 PM
TucsonJack Re: Search for string across... 05-09-2012, 06:25 PM
StevenM Re: Search for string across... 05-09-2012, 07:45 PM
TucsonJack Re: Search for string across... 05-14-2012, 02:09 PM
  1. #1
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Search for string across header row, then search for another string down found column

    I think I got this to work.
    Backup your data.

    Sub MoveCommentColumns()
        Const sFind As String = "Comment"
        Dim nPasteCol As Long, nCol As Long, nLastCol As Long, nLastRow As Long
        Dim rg As Range, i As Long
    
        'Paste after column H
        nPasteCol = 8
    
        '  Establish range of data
        nLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        nLastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
        
        ' Search for "Comment" string in header row
        For nCol = 1 To nLastCol
            If InStr(1, Cells(1, nCol), sFind, vbTextCompare) > 0 Then
                ' Search in Comment Column for any text greater than 4
                Set rg = Range(Cells(2, nCol), Cells(nLastRow, nCol))
                For i = 1 To rg.Rows.Count
                    If Len(rg.Cells(i, 1)) > 4 Then
                        rg.EntireColumn.Select
                        rg.EntireColumn.Cut
                        ' Insert the cut column in successive columns following column "H"
                        nPasteCol = nPasteCol + 1
                        Columns(nPasteCol).Insert Shift:=xlToRight
                        Exit For
                    End If
                Next i
            End If
       Next nCol
    End Sub

  2. #2
    Registered User
    Join Date
    11-17-2011
    Location
    Tucson, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Search for string across header row, then search for another string down found column

    StevenM,

    Thank you so much. Your most elegant (compared to the clunky way I was approaching the problem) solution worked like a charm. I will study how you did this. I have much to learn.

    I haven't discovered how to assign credit to your contribution. I hope the "solved" marker does so. Again... amazingly fine work.. Thank you.

    TucsonJack

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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