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.
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 |
---|---|---|---|
How to convert from xls to xlsx using java | 7 | 35 | |
modify formula same cell multiple times | 15 | 28 | |
Excel VBA | 10 | 38 | |
Excel formula Sumif not working | 4 | 26 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
12 Experts available now in Live!