Solved

Insert formula into multiple cells in Excel Spreadsheet, via VBA

Posted on 2011-09-08
13
940 Views
Last Modified: 2012-05-12
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:

intCol = 3
While ActiveSheet.Cells(3, intCol) <> ""
    strFormula = "=IF(Sum(C4:C400) = 0, """", ""Out of Balance"")"
    ActiveSheet.Cells(2, intCol).FormulaR1C1 = strFormula
    intCol = intCol + 1
Wend

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?
0
Comment
Question by:Dale Fye (Access MVP)
  • 8
  • 5
13 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 36503865
I would suggest:

dim rngSum as range

set sht = ActiveSheet
set rngSum = sht.Range("C4",sht.Range("C" & sht.Rows.Count).End(xlUp)) 'will look from bottom up, to define the bottom end of the range

intCol = 3
While sht.Cells(3,intCol) <> ""
   strFormula = "=IF(Sum(" & rngSum.Offset(,3-intCol).Address & ") = 0,"""",""Out of Balance"")"
   intCol = intCol + 1
Wend

Does this help?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36503940
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

dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36503948
Sorry, that last post was supposed to start with:

Here's another approach...  This one doesn't require a loop.

Enjoy!

Dave
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 36504065
Dave,

I like the 2nd approach and will give it a try:

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:

Range(sht.Cells(4,3), sht.Cells(4, 3))

Dale
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36504167
Ahhh - right, otherwise negative columns! lol

your example is correct;  when you know the row and column numbers, the cells method is the right approach...

to get a column number when you have a letter, you could use:

dim myCol as Long

myCol = Range("whatever").Column (make sure only one column is in the range definition)

myRow = Range("whatever").Row (make sure only one row is in the range definition)


>> But I tried about a half dozen methods to assign strFormula to the cell, and could not get the syntax right.
Apologies.  the formula assignment in the first approach should have been:

intCol = 3
While sht.Cells(3,intCol) <> ""
   strFormula = "=IF(Sum(" & rngSum.Offset(,3-intCol).Address & ") = 0,"""",""Out of Balance"")"
   sht.Cells(3.intCol).formula = strFormula
   intCol = intCol + 1
Wend




Dave
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 36504200
Dave,

With the 2nd approach:

1.  Had to remove "Set" from in front of maxRow and maxCol

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 right
    
End Sub

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 41

Expert Comment

by:dlmille
ID: 36504702
1.  sorry about that.  I'm properly corrected - should have at least checked - I was typing it in on the fly.  Doing that now!

2.  from the bottom:

maxCol = sht.Range("C").end(xlUp).Column

3.  of course, you're right... we need to make rngSum.Address a different form, like:

    sht.Range("C2").Formula = "=IF(Sum(" & strSum.Address(False, False) & ") = 0,"""",""Out of Balance"")" 'put the formula in the first cell

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 right
    
End Sub

Open in new window


See attached demo workbook:

sumFormula-r1.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36504731
And...

Here's the version finding the bottom maxRow going from bottom, up:
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 right
    
End Sub

Open in new window

sumFormula-r2.xls
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 36505321
Dave,

Thanks for the assistance.  I'll take a look.

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?
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 36506901
dim fRange as range

    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 Range
Dim sht As Worksheet
Dim intCol As Long, maxCol As Long, maxRow As Long
Dim 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 right
    
End Sub

Open in new window


Note, this code should really be in a public module.  I've done that in the attached.

Cheers,

dave
sumFormula-r4.xls
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 36514555
Dave,

Thanks, does exactly what I needed.

However, I'm a bit confused.  Why did you define strSum as a range, instead of a string?  Why did we need:

Set strSum = sht.Range("C$4", sht.Range("C$" & maxRow))
sht.Range("C2").Formula = "=IF(Sum(" & strSum.Address(False, False) & ") = 0,"""",""Out of Balance"")"

instead of defining strSum as a string and using:

sht.Range("C2").Formula = "=IF(Sum(C$4:C$" & maxRow
& ") = 0,"""",""Out of Balance"")"

Interestingly, your code results in a formula in  C2 that reads:

=IF(SUM(C4:C34) = 0,"","Out of Balance")

But mine returns:
 =IF(SUM(C$4:C$34) = 0,"","Out of Balance")

In both instances, the FillRight portion works as planned.

Thanks again.

Dale
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36514641
its a style issue, I suppose.

Cheers,

Dave
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 36514987
Thanks again.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now