+ Reply to Thread
Results 1 to 6 of 6

Macro: Applying formula to multiple cells in excel (formula editing)

  1. #1
    Registered User
    Join Date
    11-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Unhappy Macro: Applying formula to multiple cells in excel (formula editing)

    Hi Eveyone,

    Good Morning! I need with editing some codes. I got this code from a previous thread. But Im really having problem editing the code.
    I do not know how to apply the formula from the macro into all cells. It gives me the same result in all cells.

    The macro applies "=VLOOKUP(C8,'[Employee Database.xlsx]Employee Database'!$C:$X,22,FALSE)" this formula in all cells in column E.
    Is there a way to change this like as if it was being dragged in a simple excel.

    Sample
    Cell E8 will apply =VLOOKUP(C8,'[Employee Database.xlsx]Employee Database'!$C:$X,22,FALSE)"
    Cell E9 will apply =VLOOKUP(C9,'[Employee Database.xlsx]Employee Database'!$C:$X,22,FALSE)"
    Cell E10 will apply =VLOOKUP(C10,'[Employee Database.xlsx]Employee Database'!$C:$X,22,FALSE)" and so on. . . . .

    Please help me out on this. . .

    sample tracker.xlsm

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro: Applying formula to multiple cells in excel (formula editing)

    Are you inputting the formulae via macro? Why are you not directly feeding the formula into the file and dragging down?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Macro: Applying formula to multiple cells in excel (formula editing)

    this will do in your example.

    Please Login or Register  to view this content.
    Please take time to read the forum rules

  4. #4
    Registered User
    Join Date
    11-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Macro: Applying formula to multiple cells in excel (formula editing)

    Thanks Steffen. I tried the code but it doesn't seem to work. . . did i miss anything?

    Please Login or Register  to view this content.
    Hi Arlu - The reason as to why input the formula via macro is to protect the formula from being edited by non admins. as much as possible the instruction given to us is to hide all formulas. So Im also trying to study this as I can use this for other trackers as well.

  5. #5
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Macro: Applying formula to multiple cells in excel (formula editing)

    Sorry, made a little mistake, try now

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Macro: Applying formula to multiple cells in excel (formula editing)

    Hi Steffen Thomsen,

    I tried the code i still get an error. "C" seems to be not accepted.

    .Formula = "=VLOOKUP("C" & target.row,'[Employee Database.xlsx]Employee Database'!$C:$AR,42,FALSE)"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1