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
Title | # Comments | Views | Activity |
---|---|---|---|
conditional formatting formula error | 4 | 16 | |
Copying and pasting pictures from Excel | 2 | 16 | |
Input box prompt to specify column letter using VBA | 4 | 8 | |
How to check with VBA if an excel formula has multiple terms in it? | 2 | 15 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
6 Experts available now in Live!