using a macro to put formulas in cells.

**Simi** · 2012-05-10, 12:52 AM

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

**Ken Puls** · 2012-05-10, 06:37 AM

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.

**Bob Phillips** · 2012-05-10, 10:26 AM

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)"

**Simi** · 2012-05-10, 04:29 PM

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.

**Simi** · 2012-05-10, 05:51 PM

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

**Bob Phillips** · 2012-05-10, 10:23 PM

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"")"

**Simi** · 2012-05-15, 11:34 PM

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.

**Simi** · 2012-05-15, 11:40 PM

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

**Ken Puls** · 2012-05-16, 06:10 AM

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!