+ Reply to Thread
Results 1 to 3 of 3

Search in every column for a value in a specific row, then insert a column before if match

Hybrid View

manueslapera Search in every column for a... 05-18-2012, 03:00 PM
Pichingualas Re: Search in every column... 05-18-2012, 04:25 PM
Pichingualas Re: Search in every column... 05-18-2012, 04:48 PM
  1. #1
    Registered User
    Join Date
    03-17-2011
    Location
    madrid
    MS-Off Ver
    Excel 2007
    Posts
    2

    Search in every column for a value in a specific row, then insert a column before if match

    Hi!, I am pretty new to vba, and I am trying to write a macro that performs the following:

    For every sheet in the workbook:
    1. Select a column
    2. Search in the selected column for a specific value in the Row number 5
    3. If that value matches a word, then Insert two columns before the selected column

    4. Perform the same for every column in the sheet. (i mean, those columns where there is something, I know that there are an infinite amount of potential columns).

    Any help or direction toward an answer would be more than helpful. I could also upload a sample file if that helps.

  2. #2
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: Search in every column for a value in a specific row, then insert a column before if m

    Please do upload the sample file. What word do you need to match it with? Or did you mean, "if what's in column x row 5 is a word then insert two columns"?
    .?*??)
    `?.???.?*??)?.?*?)
    (?.?? (?.?
    Pichingualas <---
    ??????????????????????????

    Wrap your code with CODE TAGS.
    Thank those who helped you, Don't forget to add to their REPUTATION!!! (click on the star below their post).
    Please mark your threads as [SOLVED] when they are (Thread Tools->Mark thread as Solved).

  3. #3
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: Search in every column for a value in a specific row, then insert a column before if m

    This works if what you want is to add columns every time you have at least 1 character that is a letter, no matter if its lower or upper case:
    Option Explicit
    
    Sub InsertCol()
    Dim ws As Worksheet
    Dim LastCol As Long
    Dim CurrentCol As Long
    
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        LastCol = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
        CurrentCol = 1
        Do While CurrentCol <= LastCol
            If ws.Cells(5, CurrentCol).Value Like "*[A-z]*" Then
                Cells(5, CurrentCol).EntireColumn.Insert Shift:=xlToRight
                Cells(5, CurrentCol).EntireColumn.Insert Shift:=xlToRight
                CurrentCol = CurrentCol + 2
                LastCol = LastCol + 2
            End If
            CurrentCol = CurrentCol + 1
        Loop
    Next ws
    Application.ScreenUpdating = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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