Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: using a macro to put formulas in cells.

  1. #1
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0

    using a macro to put formulas in cells.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to automate a totals sheet for data I have parsed from a larger file.
    My current code creates a new workbook and adds data to a specified number of sheets, 1-12.
    I am trying to add a sheet 13 named "Totals" that will give a totals from sheets 1-12.
    I am unsure how to get a formula to be input into a cell from code without hard coding each cell.
    In the following code, I am trying to get a sum for columns B to S of rows 11 to 22. so B9 =SUM(B11:B22)
    Code:
    For cellCount = 2 To 19
          'Worksheets("Totals").Range(Cells(9, cellCount)).Value = "=SUM(cells(11,cellcount):cells(22,cellcount))"
    Next
    I get a runtime error at this line in the code.

    I am trying to use a loop to eliminate all the hard coding. Each of my columns B-S rows 11 to 22 will have similar loops for the formulas.
    B11=SUM('1'!M:M)
    B12=SUM('2'!M:M)
    B13=SUM('3'!M:M)

    C11=COUNTIF('1'!G:G,"H")
    C12=COUNTIF('2'!G:G,"H")
    C13=COUNTIF('3'!G:G,"H")

    Thank you,

    Simi

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Simi,

    Using .Value is going to put the value in the cell, not the formula. So to put the formula in B9 you could use:
    Code:
    Worksheets("Totals").Range("B9").Formula = "=SUM(B11:B22)"
    To work more dynamically though, you want to use FormulaR1C1, which refers to the formula in R1C1 notation. So for B9 you'd do:
    Code:
    Worksheets("Totals").Range("B9").FormulaR1C1 = "=SUM(R[2]C:R[13]C)"
    Hopefully that gives you enough to get you started.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Your problem is not the use of the Value property, VBA stupidly allows you to inject a formula with that, but the fact that you are trying to use VBA objects as part of the formula, and the redundant Range(Cells(... You need to construct the formula with these objects, not just embed them

    Code:
    For cellcount = 2 To 19    Worksheets("Totals").Cells(9, cellcount).Value = _
            "=SUM(" & Range(Cells(11, cellcount), Cells(22, cellcount)).Address(False, False) & ")"
    Next cellcount
    But you should use Formula anyway

    Code:
    For cellcount = 2 To 19
        Worksheets("Totals").Cells(9, cellcount).Formula = _
            "=SUM(" & Range(Cells(11, cellcount), Cells(22, cellcount)).Address(False, False) & ")"
    Next cellcount
    or FormulaR1C1

    Code:
    For cellcount = 2 To 19
        Worksheets("Totals").Cells(9, cellcount).FormulaR1C1 = _
            "=SUM(R11C" & cellcount & ":R22C" & cellcount & ")"
    Next cellcount
    As all of the formulae are the same relatively, you don't even need a loop

    Code:
        Worksheets("Totals").Range("B9:S9").Formula = "=SUM(B11:B22)"

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Ken/Bob,

    Thank you for the posts, I recorded a macro of me putting in the formula in the cell b9 and it used the R1C1 notation. I did not understand what it meant, but now I gather the R1C1 notation is rows and columns relative to the starting cell.

    Simi

    P.S.
    Thank you again for having this site with so many helpful people willing to give advice.

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Just an update, I ran into a problem again, but figured it out. I still don't know why it was a problem but anyway.
    this code works perfectly.

    Code:
    Worksheets("Totals").Range("B9:S9").FormulaR1C1 = "=SUM(R[2]C:R[13]C)"
    this code was giving me trouble.
    Code:
    Worksheets("Totals").Range(Cells(cellCount + 10, 2)).Formula = "=SUM('" & cellCount & "'!M:M)"
    but I changed it to, this and it works.
    Code:
    Worksheets("Totals").Cells(cellCount + 10, 2).Formula = "=SUM('" & cellCount & "'!M:M)"
    I don't know why but using Range and Cells together was not working for me.

    And I had an interesting problem where I need to have quotes in my formula, but was able to get around that by using chr(34) instead of "
    Code:
    Worksheets("Totals").Cells(cellCount + 10, 5).Formula = "=COUNTIF('" & cellCount & "'!G:G," & Chr(34) & "H" & Chr(34) & ")"
    Thanks again for the help

    Simi

  6. #6
    That Range(Cells(... issue was one of the things I mentioned in my response. Cells is a property of the Worksheet object or a property of the Range object. So you can use

    Code:
    Worksheets("sheetname").Cells(rownum,colnum)
    or you can use Cells within range, like so
    Code:
    Worksheets("sheetname").Range(Worksheets("sheetname").Cells(rowstart, colstart), Worksheets("sheetname").Cells(rowend, colend))
    Note two things here, that when Cells is used as a proprty of Range, you specify the start and end of the range. And you should qualify Cells as well as range with its worksheet parent.


    When you have quotes within string you have to double-up. Chr(34) is one way of quotes, but I prefere this way

    Code:
    Worksheets("Totals").Cells(cellCount + 10, 5).Formula = "=COUNTIF('" & cellCount & "'!G:G," & """H"")"

    Last edited by Bob Phillips; 2012-05-10 at 10:28 PM.

  7. #7
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Ken/Bob

    You guys have been a huge help to my understanding of how to program with the VBA.
    And I really like the easier way to use the quotes Bob, that makes looking at the code much easier than trying to remember what chr(34) is.

    I ran into another problem with this new totals sheet that I was creating.
    I am trying to format the sheet so I can read the data better, and I can not seem to get the autofit to work.
    I have columns from A to AE and have the problem that column A wont autofit. The rest of the columns all seem to autofit correctly, but column A refuses to cooperate.

    I have tried the following.

    Code:
    Worksheets("Totals").UsedRange.Columns.AutoFit
    Code:
    Worksheets("Totals").Cells.EntireColumn.AutoFit
    Worksheets("Totals").Cells.EntireRow.AutoFit
    Code:
    Cells.Select
    Cells(1, 1).Activate
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    All of these have had the same results. It isn't a huge problem, but more of an annoyance since the rest of the columns seem to be working appropriately.

    Simi.

  8. #8
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    So it seems I was doing the autofit before I copied all the data to the sheets. It works correctly if you autofit after you have data, and not just headers.

    Simi

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Simi,

    That would make sense. If there's no data in the sheet it will autofit to based on defaults I assume.

    PS, Bob, I actually wasn't aware that you could inject a formula using .Value. Probably never tried it as it shouldn't work. That IS stupid!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Quote Originally Posted by Ken Puls View Post
    PS, Bob, I actually wasn't aware that you could inject a formula using .Value. Probably never tried it as it shouldn't work. That IS stupid!
    I agree Ken.

    It is similar to being able to put a value into a formula property

    Code:
    Activecell.FormulaR1C1 = "Account num"
    VBA is incredibly lax on data typing.

Page 1 of 2 1 2 LastLast

Posting Permissions

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