Results 1 to 8 of 8

Thread: Paste formula in empty cells next to a variable range

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Paste formula in empty cells next to a variable range

    Im looking for a vba solution to paste a formula on the empty cells in Column D next to a variable range in column A, any help will be very appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Paste formula in empty cells next to a variable range

    What is the variable range in A & what is the formula?
    - 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
    Oct 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste formula in empty cells next to a variable range

    Quote Originally Posted by Fluff View Post
    What is the variable range in A & what is the formula?
    Thanks Fluff, i might have used the wrong words, by variable range I meant total rows in column A will be changing every day.

    The formula is:
    "=IFERROR(HLOOKUP(RC[-3],DataForm!R3C3:R12C3,9,0),"""")"

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Paste formula in empty cells next to a variable range

    Ok, do you want that formula in all col D down to the last row in col A, or just from the last used cell in D down to the last cell in col A, or something else?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste formula in empty cells next to a variable range

    Quote Originally Posted by Fluff View Post
    Ok, do you want that formula in all col D down to the last row in col A, or just from the last used cell in D down to the last cell in col A, or something else?
    -The formula must be inserted in all blank cells in column D down to the last row in Column A, examples:

    Example 1
    Colums A Column D
    P12345 Paste formula here
    P12344 Existent value
    P15677 Paste formula here
    P12456 Paste formula here

    Example 2
    Column A Column D
    P12985 Existent value
    P12644 Existent value
    P15932 Paste formula here
    P12546 Existent value

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Paste formula in empty cells next to a variable range

    Ok, how about
    Code:
    Sub davidmg()
       With Range("D1:D" & Range("A" & Rows.Count).End(xlUp).Row)
          .SpecialCells(xlBlanks).FormulaR1C1 = "=IFERROR(HLOOKUP(RC[-3],pcode!R3C3:R12C3,9,0),"""")"
       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

  7. #7
    New Member
    Join Date
    Oct 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste formula in empty cells next to a variable range

    Quote Originally Posted by Fluff View Post
    Ok, how about
    Code:
    Sub davidmg()
       With Range("D1:D" & Range("A" & Rows.Count).End(xlUp).Row)
          .SpecialCells(xlBlanks).FormulaR1C1 = "=IFERROR(HLOOKUP(RC[-3],pcode!R3C3:R12C3,9,0),"""")"
       End With
    End Sub
    Exactly what I need it, thank you for your time.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Paste formula in empty cells next to a variable range

    Glad to help & 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
  •