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
    208

    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,415

    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 01: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
    208

    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
    208

    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,415

    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
    208

    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,415

    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
    208

    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
    208

    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,415

    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.

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