Results 1 to 4 of 4

Thread: macro relative position
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2014
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default macro relative position

    I have data that looks like that:
    a 1
    b 5
    c 3
    d 7
    e 2
    f 2
    g
    h
    i
    j
    I want to fill the missing data in column b. that will be paralel to column a.
    I wrote the attached macro
    Range("B1").Select
    Selection.End(xlDown).Select
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C"
    Range("B8").Select
    Selection.AutoFill Destination:=Range("B8:B16")
    Range("B8:B16").Select
    End Sub

    my problem is that this macro does not work properly if column b is shorter or longer. How can i correct the macro that regardless the column length it will fill he missing data.
    Thanks

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    468 Post(s)
    Tagged
    47 Thread(s)

    Default Re: macro relative position

    How about
    Code:
    Sub Tody03()
       With Range("B:B")
          .SpecialCells(xlBlanks)(1).Value = 0
          .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jun 2014
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: macro relative position

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub Tody03()
       With Range("B:B")
          .SpecialCells(xlBlanks)(1).Value = 0
          .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
       End With
    End Sub

    It is so simple but working Thanks
    Tody

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    468 Post(s)
    Tagged
    47 Thread(s)

    Default Re: macro relative position

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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