COURSE of the MONTH

Insert formula into multiple cells in Excel Spreadsheet, via VBA

I'm trying to figure out the best way to insert a similar formula into multiple columns within a spreadsheet, via VBA.

Disclaimer: I'm an Access developer, branching out into Excel.

My client would like some code that will review a worksheet, compare values in that worksheet to another worksheet in the same workbook, and insert rows and columns as necessary (I have this part working). But the cell in the 2nd row of each column contains a formula that checks to see whether the Sum() of all the values from the 4th row through the Nth row (determined at runtime) is zero. If so, the returned value is an empty string, if not, the returned value is "Out of Balance". I don't have the exact syntax with me, but I beleive it looks like:

=IF(Sum(C4:C400) = 0, "", "Out of Balance")

They then have the conditional formatting set to display that cell in yellow if "Out of Balance".

What is the best way to refer to these cell ranges as I loop through the columns and set both the formula and the conditional formatting? I was working with something along the lines of:

But I'm not sure what the best way is to establish the range (column letter and start/end row) when this worksheet will grow both horizontally and vertically. What is the best method for referencing the "C4:C400" piece of this?

Who is Participating?

Set fRange = sht.Cells.Find(what:="*", LookIn:=xlFormulas, searchdirection:=xlPrevious)
If fRange Is Nothing Then Exit Sub ' otherwise, its an empty worksheet

maxRow = fRange.Row

See attached revised code using this for maxRow. See shaded yellow cell - that column has more rows, and all formulas index their last row the same way as fRange.Row.

Public Sub AddFormula()Dim strSum As RangeDim sht As WorksheetDim intCol As Long, maxCol As Long, maxRow As LongDim fRange As Range Set sht = ActiveSheet 'maxRow = sht.Range("C" & sht.Rows.Count).End(xlUp).Row 'looks from C4 down till finds a blank Set fRange = sht.Cells.Find(what:="*", LookIn:=xlFormulas, searchdirection:=xlPrevious) If fRange Is Nothing Then Exit Sub ' otherwise, its an empty worksheet maxRow = fRange.Row maxCol = sht.Range("C4").End(xlToRight).Column 'looks from C4 to the right till finds a blank Set strSum = sht.Range("C$4", sht.Range("C$" & maxRow)) 'defines the entire column to have this formula. Note $ signs because this gets copied to the right sht.Range("C2").Formula = "=IF(Sum(" & strSum.Address(False, False) & ") = 0,"""",""Out of Balance"")" 'put the formula in the first cell sht.Range("C2", sht.Cells(2, maxCol)).FillRight 'equivalent of a "drag or copy" of the formula to the rightEnd Sub

Dim rngSum As Range
Dim sht As Worksheet
Dim intCol As Long, maxCol As Long, maxRow As Long

Set sht = ActiveSheet
Set maxRow = sht.Range("C4").End(xlDown).Row 'looks from C4 down till finds a blank
Set maxCol = sht.Range("C4").End(xlToRight).Column 'looks from C4 to the right till finds a blank

Set rngSum = sht.Range("C$4", sht.Range("C$" & maxRow)) 'defines the entire column to have this formula. Note $ signs because this gets copied to the right
sht.Range("C3").Formula = "=IF(Sum(" & rngSum.Address & ") = 0,"""",""Out of Balance"")" 'put the formula in the first cell
sht.Range("C3", sht.Cells(3, maxCol)).FillRight 'equivalent of a "drag or copy" of the formula to the right

In the 1st approach I had to change:rngSum.Offset(,3-intCol)
to:rngSum.Offset(,intCol-3)

But I tried about a half dozen methods to assign strFormula to the cell, and could not get the syntax right.

How do you refer to a column in the Range object, when you know the row and column #'s? I know I could build the string "C4" with a combination of Choose(intCol, "A", "B", "C", ...) & intRow, but is there another way, similar to:

2. I have some rows that may be blank in column "C". How do I find the first row (from) the bottom of the spreadsheet which contains a value in any of the columns from "C" to maxCol?

3. The formula that is actually being inserted in "C2" is: =IF(SUM($C$4:$C$10) = 0,"","Out of Balance")

How do I get rid of the $ in front of the "C" so that the FillRight method works across all columns.

Public Sub AddFormula Dim rngSum As Range Dim sht As Worksheet Dim intCol As Long, maxCol As Long, maxRow As Long Set sht = ActiveSheet maxRow = sht.Range("C4").End(xlDown).Row 'looks from C4 down till finds a blank maxCol = sht.Range("C4").End(xlToRight).Column 'looks from C4 to the right till finds a blank Set rngSum = sht.Range("C$4", sht.Range("C$" & maxRow)) 'defines the entire column to have this formula. Note $ signs because this gets copied to the right sht.Range("C2").Formula = "=IF(Sum(" & rngSum.Address & ") = 0,"""",""Out of Balance"")" 'put the formula in the first cell sht.Range("C2", sht.Cells(2, maxCol)).FillRight 'equivalent of a "drag or copy" of the formula to the rightEnd Sub

strSum.Address(False, False) <- the first parameter is for Row, second is for Column. TRUE - use absolute reference with $, FALSe - don't

In this case, we could put a $ in the row portion, but as it doesn't matter, using False for both parameters seems reasonable.

This is TESTED :)

Here's the resulting code:

Public Sub AddFormula() Dim strSum As Range Dim sht As Worksheet Dim intCol As Long, maxCol As Long, maxRow As Long Set sht = ActiveSheet maxRow = sht.Range("C4").End(xlDown).Row 'looks from C4 down till finds a blank maxCol = sht.Range("C4").End(xlToRight).Column 'looks from C4 to the right till finds a blank Set strSum = sht.Range("C$4", sht.Range("C$" & maxRow)) 'defines the entire column to have this formula. Note $ signs because this gets copied to the right sht.Range("C2").Formula = "=IF(Sum(" & strSum.Address(False, False) & ") = 0,"""",""Out of Balance"")" 'put the formula in the first cell sht.Range("C2", sht.Cells(2, maxCol)).FillRight 'equivalent of a "drag or copy" of the formula to the rightEnd Sub

Public Sub AddFormula() Dim strSum As Range Dim sht As Worksheet Dim intCol As Long, maxCol As Long, maxRow As Long Set sht = ActiveSheet maxRow = sht.Range("C" & sht.Rows.Count).End(xlUp).Row 'looks from C4 down till finds a blank maxCol = sht.Range("C4").End(xlToRight).Column 'looks from C4 to the right till finds a blank Set strSum = sht.Range("C$4", sht.Range("C$" & maxRow)) 'defines the entire column to have this formula. Note $ signs because this gets copied to the right sht.Range("C2").Formula = "=IF(Sum(" & strSum.Address(False, False) & ") = 0,"""",""Out of Balance"")" 'put the formula in the first cell sht.Range("C2", sht.Cells(2, maxCol)).FillRight 'equivalent of a "drag or copy" of the formula to the rightEnd Sub

Think you misunderstood #2. How do I determine the first ROW from the bottom of the spreadsheet, in any of the columns that I'm interested in, that contains a value, so it "C400", "D403", and "E415" all contain a value (any value) how do I find row 415?