Results 1 to 3 of 3

Thread: Help!!! Find column and define with name

  1. #1

    Help!!! Find column and define with name



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a problem with my code. First off I am new to coding. Researched different sites online and the code I wrote is the best I came up with.
    What I am trying to do with my code is search for the column name in Row 1 of each column (A1, B1, C1, ect..). After it finds the first two or three characters of the name of the column. Select that column and define that column by the name I supplied. So I can use that new define name in a formula I created. The problem that I have is I have two column with similar names and it always define the one that I do not want. One column is (B1) "KbTVD" the one I dont want defined and (F1) "TVD" the one I need. Every time I run my code it selects and defines B1 and I need F1 to be defined instead. Also I need my code to search different row 1 columns just for "TVD" because it is not a guarantee that "TVD" will always be in F1.

    Code:

    Code:
    Sub DATA()  
    
        Set fnd = Range("1:1").Find("on*")   
        ActiveWorkbook.Names.Add "ono", fnd.EntireColumn, True
    
        Set fnd = Range("1:1").Find("tvd")
        ActiveWorkbook.Names.Add "tvd", fnd.EntireColumn, True
    
    End Sub
    this code selects and defines B1

    Code:
    Sub DATA()  
    
        Set fnd = Range("1:1").Find("on*")   
        ActiveWorkbook.Names.Add "ono", fnd.EntireColumn, True
        
        Set fnd = Range("1:1").Find("tvd", , , , , , True) ' 
        ActiveWorkbook.Names.Add "tvd", fnd.EntireColumn, True
    
    
    End Sub
    I tried to rewrite it and I get a error

    run-time error '91':

    object variable or with block variable not set


    Also do I need to Dim something in my code?

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Maybe have a look at this page and note you can specify the find method to look for whole or part words.

    Also note the third line in the remarks section about what unspecified settings get used.

  3. #3
    i need solution for this as well

Posting Permissions

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