+ Reply to Thread
Results 1 to 12 of 12

Using Excel VBA to create formulas in cells dependant on user input

  1. 04-23-2011, 10:00 AM #1
    erik11
    erik11 is offline
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Microsoft 365
    Posts
    13

    Using Excel VBA to create formulas in cells dependant on user input

    Dear Excel Users

    I am trying to write a VBA macro, which can create a worksheet with content. When this macro has been executed one time it won't be used anymore. The resulting worksheet should work on its own.

    I am unsure how to create formulas in the worksheet. Let's assume I wanted to place the formula "sum(A1:A5) in cell B4. Then I would just write the following in a code line:

    Cells(4,2) = "=sum(A1:A5)"

    The formulas are however not fixed as in the above example, but depends on user input. Suppose the user has delivered information about what cells to sum and what place to put that formula. Let's say I have defined two variables FR and FC containing the row and the column of the cell to place the formula, and suppose I have defined variables FromR, FromC, ToR and ToC specifying the range to sum. How do I write a code line to place the userspecified formula in the userspecified cell? I mean I can't just replace A1 and A5 with the userspecified range ...

    I hope someone can help solving this problem. I have considered the possibility of using names for ranges.

    Regards,

    Erik
    Last edited by erik11; 04-24-2011 at 06:57 PM.
    Reply With QuoteRegister To Reply
  2. 04-23-2011, 10:32 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Using Excel VBA to create formulas in cells dependant on user input

    If I have understood correctly:

    Cells(4,2) = "=sum(A1:A5)"

    would become:

    Cells(FR, FC) = "=SUM(" & FromC & FromR & ":" & ToC & ToR & ")"


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 04-23-2011, 10:38 AM #3
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Using Excel VBA to create formulas in cells dependant on user input

    Hi erik11

    Excel VBA has a Range.Formula and Range.FormulaR1C1 commands that should do what you need (If I understand your question)

    If in VBA you define FR = 10 and FC = 23 and you want the sum from some other rows say from top

    I believe you woud write the code something like:

    Cells(FR,FC).Formula = your formula.

    When I need to do the "your formula" part of the above, I always do it in the cell using the Excel Function first. Then I copy and paste it into the "your formula" spot above. Then put quote marks in the front of the equal sign and at the end of the string. Then double quote every quote in the pasted string. This seems to work for me.

    hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  4. 04-23-2011, 09:19 PM #4
    erik11
    erik11 is offline
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Microsoft 365
    Posts
    13

    Re: Using Excel VBA to create formulas in cells dependant on user input

    My basic problem is to make Excel VBA translate the knowledge of cell and row numbers into something useful for a formula. For example the cells in column 1 from row 1 to row 5 should be translated into A1:A5 to be used in the formula SUM(A1:A5).

    Thanks MarvinP, for referring me to the Range.FormulaR1C1 command. This seems to be helpful in the way that it uses the R1C1 notation instead of the A1 notation. I still need something to make it work though. I want to write the following codeline:

    Cells(FR,FC).FormulaR1C1 = "=SUM(???)"

    but I need to replace ??? with something that work. If the range was fixed like A1:A5 I could just replace ??? with R1C1:R5C1. Remember however that this range is not fixed but specified by the variables FromR, FromC, ToR and ToC. In this example they have the values 1, 1, 5 and 1 respectively. Maybe I need to piece it together a little like TMShucks suggest? Is it possible to go this way?

    Regards,

    Erik
    Reply With QuoteRegister To Reply
  5. 04-23-2011, 09:31 PM #5
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Using Excel VBA to create formulas in cells dependant on user input 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 04-23-2011, 10:40 PM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Using Excel VBA to create formulas in cells dependant on user input

    Taking Nimrod's code as a base, the equivalent code for my suggestion is:

    Please Login or Register  to view this content.

    You get, in cell A1: =SUM(E2:G5)

    Regards
    Reply With QuoteRegister To Reply
  7. 04-24-2011, 04:26 PM #7
    erik11
    erik11 is offline
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Microsoft 365
    Posts
    13

    Re: Using Excel VBA to create formulas in cells dependant on user input

    Great! Thanks to both of you guys. Either of your codes work. Here I need to reveal more, since my initial job is not solved completely yet. Actually I am a high school teacher, who want to write a program generating a table (not an Excel Table) for math tests I want to deliver the number of students and the number of exercises and have the VBA code generate the corresponding testtable for me. The names of the students are supposed to be placed in the first column manually, starting in the second row and continuing below. Then the exercise scores are supposed to be placed manually - after the testtable has been generated - from column 2 and forth. Now suppose I have 20 students and 10 exercises. Then I want a final column (after the columns containing the results of each exercise) containing the total scores of the individual students. So with the numbers chosen, the formula containing the total score of student 1 is to be placed in row 2 and column 2+10 = 12. The formula containing the total score of student 2 is to be placed in row 3 and column 12, and so forth... I hope you can follow me?

    Now I know about the syntax of the code to create a formula in a variable cell. But what if I need a whole array of formulas like in the above example? How do I attack this problem in the smartest way?

    So far, thank you so much for your help!

    Regards,

    Erik
    Reply With QuoteRegister To Reply
  8. 04-24-2011, 04:29 PM #8
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Using Excel VBA to create formulas in cells dependant on user input

    Why not just use a Pivot Table for the entire table ?
    Reply With QuoteRegister To Reply
  9. 04-24-2011, 04:43 PM #9
    erik11
    erik11 is offline
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Microsoft 365
    Posts
    13

    Re: Using Excel VBA to create formulas in cells dependant on user input

    Maybe! Actually I am not really ware of how a Pivot table works, to be honest.

    Erik
    Reply With QuoteRegister To Reply
  10. 04-24-2011, 04:55 PM #10
    erik11
    erik11 is offline
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Microsoft 365
    Posts
    13

    Re: Using Excel VBA to create formulas in cells dependant on user input

    When thinking about it more closely, I think I could just use your code and write a FOR loop ...

    Erik
    Reply With QuoteRegister To Reply
  11. 04-24-2011, 05:07 PM #11
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: Using Excel VBA to create formulas in cells dependant on user input

    I'm sorry but your explanation of what you want to do is way over my head ... just don't understand what is needed.

    Having been given code to generate formulae, I expected you'd know what you were going to do with it.

    Sorry, can't really help with this.
    Last edited by TMS; 04-25-2011 at 03:49 AM.
    Reply With QuoteRegister To Reply
  12. 04-24-2011, 06:39 PM #12
    erik11
    erik11 is offline
    Registered User
    Join Date
    04-19-2011
    Location
    Denmark
    MS-Off Ver
    Microsoft 365
    Posts
    13

    Re: Using Excel VBA to create formulas in cells dependant on user input

    Forget about my latest question. Together with the code suggested it is easily solved using a For loop. Therfore my question is solved!

    Again thanks for all contributions. I appreciate it!
    Last edited by erik11; 04-24-2011 at 06:50 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1