In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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
Join the community of 500,000 technology professionals and ask your questions.