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 =
    End If

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


  • 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...


    Dim hdr as string
    Dim r as integer
    hdr = "HEADER"
    r = application.worksheetfunction.match(hdr,range("A:A"),0)
    cells(r+1,1) =
  • 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) =

  • 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
    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!