Results 1 to 5 of 5

Thread: Find a column

  1. #1
    Join Date
    Nov 2004
    Posts
    78

    Unanswered: Find a column

    The first row includes the headings of the columns.

    I have a macro and would like to add a function, that will check the heading columns if there is a heading called MyColumn, if yes, ignore, if not, add a column D and add
    the heading MyColumn
    Is this possible?

    Thank you

    Joe

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy.

    yes, it is possible. Are you wanting to insert a new column that would be Column D and everything to the right will be pushed one column to the right.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2004
    Posts
    78
    yes, you got it.

    please help
    Thanks

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Joe1
    The first row includes the headings of the columns.

    I have a macro and would like to add a function, that will check the heading columns if there is a heading called MyColumn, if yes, ignore, if not, add a column D and add
    the heading MyColumn
    Is this possible?

    Thank you

    Joe
    Why not just record a new macro and incorporate the resultant code into your existing macro.

    HTH,
    Sam

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    I'd use a loop to check the cell values in your header row. You could record some parts of this like the column insert, but most of this you will need to write code or copy from a good example and modify to meet your requirements. I'm giving 2 examples one checking the column names within a sub procedure and the other example is using a Function to return true/false based on the results. For simplicity used the number 20 to check 1-20 columns or loop until a match is found. You'd need to set a higher number if you have more columns or check the number of used cells and set a variable for the columns used.
    Code:
    ' Example checking column names
    ' used integer 20 to check through column 20
    
    Sub checkcol()
     Dim numCol As Integer
     Dim strName As String
     Dim bExists As Boolean
     Dim i As Integer
    
    numCol = 20
    strName = "MyCol"
    bExists = False
    
    For i = 1 To numCol
      colName = ActiveSheet.Cells(1, i)
      If UCase(strName) = UCase(colName) Then
        bExists = True
        Exit For
      End If
    Next
    
    If Not bExists Then
        Columns(4).Insert
        With Cells(1, 4)
          .Value = strName
          .Font.Bold = True
        End With
    End If
    
    End Sub
    
    
    ' Here is a function you can call from a sub proc.
    ' Then take action based on true or false return
    ' bColExists = fColumnExists("MyCol")
    
    Function fColumnExists(strName) As Boolean
      Dim numCol As Integer
      Dim bExists As Boolean
      Dim i As Integer
    
      numCol = 20
     
      For i = 1 To numCol
        colName = ActiveSheet.Cells(1, i)
        If UCase(strName) = UCase(colName) Then
            fColumnExists = True
          Exit For
        End If
      Next
    End Function
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •