Create a vba formula from cells

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Create a vba formula from cells

  1. #1
    Board Regular
    Join Date
    Jan 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create a vba formula from cells

     
    Need a little assistance with a vba formula to get info from cells to create a formula like this
    Code:
        Range("J1").Formula = "=IF(D1=""Mayberry R.F.D."",""Mayberry RFD""," & _
        "IF(D1=""Diners, Drive-ins and Dives"",""Diners Drive-Ins and Dives"",IF(D1=""Forever "",""Forever (2014)""," & _
        "IF(D1=""Food Factory (CA)"",""Food Factory CA"",IF(D1=""Castle (2009)"",""Castle 2009"",IF(D1=""Guy's Grocery Games"",""Guys Grocery Games"",D1))))))"
    
         Range("J1").Copy
        Range("J2:J" & LastRow).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    I am constantly adding to the formula.
    So, say column A would be what to look for, column B what to change to to.

    Thanks

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,522
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Create a vba formula from cells

    Try this...

    Code:
    Range("J1:J" & LastRow).Formula = "=IFERROR(VLOOKUP(D1,A:B,2,0),"""")"
    Last edited by AlphaFrog; May 26th, 2015 at 02:06 PM. Reason: Corrected quotes in formula
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Jan 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a vba formula from cells

    Quote Originally Posted by AlphaFrog View Post
    Try this...

    Code:
    Range("J1:J" & LastRow).Formula = "=IFERROR(VLOOKUP(D1,A:B,2,0),"""")"
    Thank you, I will give that a try!

  4. #4
    Board Regular
    Join Date
    Jan 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a vba formula from cells

    Quote Originally Posted by camle View Post
    Thank you, I will give that a try!
    If I put all the data in another worksheet, say "Changes" How pick up the sheet name.

  5. #5
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,522
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Create a vba formula from cells

    Quote Originally Posted by camle View Post
    If I put all the data in another worksheet, say "Changes" How pick up the sheet name.
    Code:
    Range("J1:J" & LastRow).Formula = "=IFERROR(VLOOKUP(D1,Changes!A:B,2,0),"""")"
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  6. #6
    Board Regular
    Join Date
    Jan 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a vba formula from cells

    Quote Originally Posted by AlphaFrog View Post
    Code:
    Range("J1:J" & LastRow).Formula = "=IFERROR(VLOOKUP(D1,Changes!A:B,2,0),"""")"
    Just about there!
    if D1 does not need the change then make it D1

    Thanks

  7. #7
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,522
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Create a vba formula from cells

    Quote Originally Posted by camle View Post
    Just about there!
    if D1 does not need the change then make it D1

    Thanks
    Code:
    Range("J1:J" & LastRow).Formula = "=IFERROR(VLOOKUP(D1,Changes!A:B,2,0),D1)"
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  8. #8
    Board Regular
    Join Date
    Jan 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a vba formula from cells

    Quote Originally Posted by camle View Post
    Just about there!
    if D1 does not need the change then make it D1

    Thanks
    OK Brain on

    Code:
    =IFERROR(VLOOKUP(D2,Changes!A:B,2,0),D1)

  9. #9
    Board Regular
    Join Date
    Jan 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a vba formula from cells

    Quote Originally Posted by camle View Post
    OK Brain on

    Code:
    =IFERROR(VLOOKUP(D2,Changes!A:B,2,0),D1)
    Sometimes things are just over thought.
    Very good solution that you have there.

    Thanks

  10. #10
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,522
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Create a vba formula from cells

      
    You're welcome. Thanks for the feedback.

    You mixed D2 and D1 in the formaula?
    =IFERROR(VLOOKUP(D2,Changes!A:B,2,0),D1)
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

User Tag List

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
  •  

 

 
DMCA.com