using a macro to put formulas in cells.

S

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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
 
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.
 
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)"
 
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.
 
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
 
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: 
[LEFT]Worksheets("Totals").Cells(cellCount + 10, 5).Formula = "=COUNTIF('" & cellCount & "'!G:G," & """H"")"
[/LEFT]
 
Last edited:
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.
 
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
 
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 said:
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!
Click to expand...

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.
 
Simi said:
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.
Click to expand...

Many disagree with me, but I think so too. In some situations, it takes a bit of trial and error to get the right number, I never remember fully the rules, but I can live with that.
 
Ironically, it never actually occured to me to use Chr(34) for putting in quotes. I've also doubled up to get them. Works with other languages like SQL too, so it can't be all bad. :)
 
I started out by always using Chr(34), but switched to embedded quotes as I just didn't like it. If you don't like embedded quotes, a better way in my view is to define a quote constant and use that.

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

Obvious and meaningful. This was my interim stage until I went fully embedded.
 
Before I knew any better, I got very familiar with the Chr() function. You see I didn't know there was a very simple way to refer to cells by number and thought it had to be done by letter.

This is the code I was using to find the last column on a sheet I had, it makes me cringe to look at now. Also it is only good from A to ZZ.

Code: 
'reset variables to figure how many columns there are for sorting
      colCnt = 75 ' start on col K
      colCnt2 = 64
      colNameMax = Chr(colCnt)
      
      
      'count how many columns to sort
      Do While IsEmpty(Worksheets(sheetName(sheetCnt)).Range(colNameMax & "1")) = False
         Worksheets(sheetName(sheetCnt)).Range(colNameMax & "3").Formula = "=sum(" & colNameMax & "9:" & colNameMax & totalRowCnt(sheetCnt) & ")"
         'colCnt = colCnt + 1
         If colCnt = 90 Then
            colCnt = 65
            colCnt2 = colCnt2 + 1
            colNameMax = Chr(colCnt2) & Chr(colCnt)
         ElseIf colCnt2 > 64 Then
            'colCnt2 = colCnt2 + 1
            colCnt = colCnt + 1
            colNameMax = Chr(colCnt2) & Chr(colCnt)
         Else
            colCnt = colCnt + 1
            colNameMax = Chr(colCnt)
         End If
      Loop
         
      'need to -1 from the column count to have accurate count
      If colCnt = 65 And colCnt2 = 65 Then
         colCnt = 90
         colNameMax = Chr(colCnt)
      ElseIf colCnt = 65 And colCnt2 > 64 Then
         colCnt = 90
         colCnt2 = colCnt2 - 1
         colNameMax = Chr(colCnt2) & Chr(colCnt)
      ElseIf colCnt > 65 And colCnt < 91 And colCnt2 > 64 Then
         colCnt = colCnt - 1
         colNameMax = Chr(colCnt2) & Chr(colCnt)
      Else
         colCnt = colCnt - 1
         colNameMax = Chr(colCnt)
      End If

oh how easier it is to do something like

Code: 
lastCol = .Cells(14, .Columns.Count).End(xlToLeft).Column

Well, just goes to show how much I have learned since finding this site ;)
 
Last edited:
You know, Simi, if the code you wrote the month before always makes you cringe it will mean you're always learning. :)
 
Ken Puls said:
You know, Simi, if the code you wrote the month before always makes you cringe it will mean you're always learning. :)
Click to expand...

If the code you wrote the month before DOESN'T make you cringe, you are incapable of improving!
 
You must log in or register to reply here.
Back
Top