Increment value in a Formula

[David Marr]

Hi,

I'm using Excel version 2003.

I have the following formula =IF(SUM('Section
3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like to
increment the cell references by 12.

I was wondering if there is a way to do this using a bit of VBA code?

So the result i'm looking for after code run is =IF(SUM('Section
3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)).

I will be running the formula along the columns so will need to increment
the formula by 12 for each new column.

Thanks
Dave

 

[Bob Phillips]

Why not just use Find & Replace, Ctrl-H?

 

[David Marr]

Because it takes too long as i have a vast amount of data.

Dave

 

[Bernard Liengme]

Are you interested in a non-VBA method?
Let's say the formulas are to be on Sheet2. The first one in D2, the next in
E2, etc.
So we have (let's forget the IF part for now)
D2 E2
F2
=SUM(H1675:J1686) =SUM(H1687:J1698) =SUM(H1699:J1710)
In D2 enter
=SUM(INDIRECT("Section3!H"&1675+12*(COLUMN(A1)-1)&":J"&1686+12*(COLUMN(A1)-1)))
Format this cell with custom format such as: 0;0;;@ to hide any zero results
Drag D2's fill handle to the right as far as you need to go.
No matter where you start your formulas, leave the reference to COLUMN(A1)
unchanged.
best wishes

 

[Gord Dibben]

Looking at your before and after examples I don't see any column changes.

H and J are consistent.

Only row increments of 12 as if you are copying down a single column.

Is that a typo or?

If you are copying down a column try this macro to increment the rows.

First remove the absolute reference $ signs from your first formula.

Sub Increment_Formula_Steps()
''copy a formula down with steps in cell references
''select range first with formula in active cell
Dim StepSize As Variant
Dim NumCopies As Integer
Dim Cell As Range
StepSize = InputBox("Step?") 'e.g., 12
If StepSize <> "" Then
NumCopies = Selection.Rows.Count
Application.ScreenUpdating = False
For Each Cell In Selection.Columns(1).Cells
Cell.Copy Cell.Offset(StepSize)
Cell.Offset(StepSize).Cut Cell.Offset(1)
Next
End If
End Sub

If you want the absolute references add them after.

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 14 Apr 2010 06:13:01 -0700, David Marr <David

 

[Bernard Liengme]

A VBA solution with zero's blanked out;
First cell used formula =Dozen(A1) to sum the first block
Drag this to the right to make =Dozen(B1) to sum next block
etc....

Function dozen(myblock)
whatblock = (myblock.Column - 1) * 12
mystart = 1675 + whatblock
firstcell = "H" & mystart
mylast = 1686 + whatblock
lastcell = "J" & mylast
myrange = "Section3!" & firstcell & ":" & lastcell
dozen = WorksheetFunction.Sum(Range(myrange))
If dozen = 0 Then dozen = ""
End Function

best wishes

 

[Dave Peterson]

It takes too long to select the range to fix
Edit|Replace
what: $1686
with: $1698
replace all

 

[David Marr]

Thanks for the reply but that doesn't seem to work.

I have the following code in another Excel file I have but i need to alter
it to ignore the Section 3 as it seems to increment that by 12.

Sub add125()
Dim rngToChange As Range
Dim iChar As Integer, iCount As Integer
Dim strOld As String, strNew As String, strNumb As String
Dim strChar As String
Set rngToChange = ActiveCell
strOld = rngToChange.Formula
strNew = ""
strNumb = ""
iCount = 0
For iChar = 1 To Len(strOld)
strChar = Mid(strOld, iChar, 1)
If IsNumeric(strChar) Then
strNumb = strNumb & strChar
Else
If strNumb <> "" Then
iCount = iCount + 1
strNew = strNew & (strNumb + IIf(iCount = 3, 0, 132))
strNumb = ""
End If
strNew = strNew & strChar
End If
Next iChar
rngToChange.Formula = strNew
End Sub

Sub add12()
Dim rngToChange1 As Range
Dim iChar1 As Integer, iCount1 As Integer
Dim strOld1 As String, strNew1 As String, strNumb1 As String
Dim strChar1 As String
Set rngToChange1 = ActiveCell
strOld1 = rngToChange1.Formula
strNew1 = ""
strNumb1 = ""
iCount1 = 0
For iChar1 = 1 To Len(strOld1)
strChar1 = Mid(strOld1, iChar1, 1)
If IsNumeric(strChar1) Then
strNumb1 = strNumb1 & strChar1
Else
If strNumb1 <> "" Then
iCount1 = iCount1 + 1
strNew1 = strNew1 & (strNumb1 + IIf(iCount1 = 3, 0, 12))
strNumb1 = ""
End If
strNew1 = strNew1 & strChar1
End If
Next iChar1
rngToChange1.Formula = strNew1
End Sub

Where am I going wrong?

cheers

Dave