dim rngSum as range
set sht = ActiveSheet
set rngSum = sht.Range("C4",sht.Range("
intCol = 3
While sht.Cells(3,intCol) <> ""
strFormula = "=IF(Sum(" & rngSum.Offset(,3-intCol).A
intCol = intCol + 1
Wend
Does this help?
Dave
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
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
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
sumFormula-r2.xls
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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Computers reporting Windows patches applied | 14 | 77 | |
Windows 7 Share with concurrent edits(Excel) | 3 | 32 | |
Vb.net dynamic formulas in runtime | 11 | 61 | |
Adding Text that self adjusts in a Cell | 8 | 32 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
18 Experts available now in Live!