using a macro to put formulas in cells.

[Simi]

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)

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]

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:

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:

Worksheets("Totals").Range("B9").FormulaR1C1 = "=SUM(R[2]C:R[13]C)"

Hopefully that gives you enough to get you started.

[Bob Phillips]

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


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


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


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


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

[Simi]

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]

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.


Worksheets("Totals").Range("B9:S9").FormulaR1C1 = "=SUM(R[2]C:R[13]C)"

this code was giving me trouble.

Worksheets("Totals").Range(Cells(cellCount + 10, 2)).Formula = "=SUM('" & cellCount & "'!M:M)"

but I changed it to, this and it works.

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 "

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

Thanks again for the help

Simi

[Bob Phillips]

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


Worksheets("sheetname").Cells(rownum,colnum)

or you can use Cells within range, like so

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



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

[Simi]

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.


Worksheets("Totals").UsedRange.Columns.AutoFit


Worksheets("Totals").Cells.EntireColumn.AutoFit
Worksheets("Totals").Cells.EntireRow.AutoFit


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]

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]

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!

[Bob Phillips]

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


Activecell.FormulaR1C1 = "Account num"

VBA is incredibly lax on data typing.

[Bob Phillips]

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.

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.

[Ken Puls]

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. :)

[Bob Phillips]

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.


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.

[Simi]

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.


'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


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

Well, just goes to show how much I have learned since finding this site ;)

[Ken Puls]

You know, Simi, if the code you wrote the month before always makes you cringe it will mean you're always learning. :)

[Bob Phillips]

You know, Simi, if the code you wrote the month before always makes you cringe it will mean you're always learning. :)

If the code you wrote the month before DOESN'T make you cringe, you are incapable of improving!

[Ken Puls]

if the code you wrote the month before doesn't make you cringe, you are incapable of improving!

lol! :)