On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
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 |
---|---|---|---|
copy same as above data | 18 | 42 | |
Creating accounts AD 2k8 PowerShell - Carriage Return in addresses | 5 | 29 | |
New Email window show up in front of any other window | 3 | 18 | |
how to return set of columns values with one column header name lookup | 2 | 32 |
Join the community of 500,000 technology professionals and ask your questions.