Inserting Name below Name Header

  • I have the following code:


    Range("A6").Value = ActiveSheet.Name


    In row A5, I have the Name Column Header. If I were to insert a row in A4, then A6 becomes A7 and my code becomes ineffective.


    I don't know what code to use but this is what I am thinking:



    Dim Rng as Range
    Set Rng = Range("A1:A50")



    If cell.value = Rng then
    Cell.offset(1,0).VALUE = Activesheet.name
    End If



    I know this code isn't going to work but I thought I would attempt to do it.



    Thanks

  • if say the header was always the same text - let's say "HEADER" then you could run a match to find the row that text appears...


    eg


    Code
    Dim hdr as string
    Dim r as integer
    hdr = "HEADER"
    r = application.worksheetfunction.match(hdr,range("A:A"),0)
    
    
    cells(r+1,1) = activesheet.name
  • Error Message


    I ran the code you provide but I receive an error message " Unable to get Match function Worksheet class".


    r = Application.WorksheetFunction.Match(hdr, Range("A:A"), 0)


    How do I fix it.

  • Have you defined hdr to be the text that will appear as the header?


    ie cell a5 = "HEADER"


    Dim hdr as string
    hdr = "HEADER"


    they must be an exact match.


    also - is the header appearing in column A? If not amend the match function range accordingly.

  • The header is "Name" and it is spelled exactly as in the following code.



    ' Dim hdr As String
    ' Dim r As Integer
    ' hdr = "Name"
    '
    ' r = Application.WorksheetFunction.Match(hdr, Range("A:A"), 0)
    ' Cells(r + 1, 1) = ActiveSheet.name

  • does it appear in column A?


    attach the file - i can't see any reason why it wouldn't work...curious


    have you checked the length of your header in the cell - ie there are no spaces like "Name " ...

  • This is what I have in column A


    Column A


    Sales Position
    FY04 Products
    Name
    Garrity, Tim
    Currency Paid

    The code is as I sent previously. Yes it is strange it wouldn't work. I just logged into Excel yesterday (starting a new assignment). Do you think that something may not be installed on the machine?





    Payout %

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!