Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment value in a Formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment value in a Formula
Why not just use Find & Replace, Ctrl-H?
-- HTH Bob "David Marr" <David wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment value in a Formula
Because it takes too long as i have a vast amount of data.
Dave "Bob Phillips" wrote: Why not just use Find & Replace, Ctrl-H? -- HTH Bob "David Marr" <David wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment value in a Formula
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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "David Marr" <David wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment value in a Formula
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 wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment value in a Formula
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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "David Marr" <David wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment value in a Formula
It takes too long to select the range to fix
Edit|Replace what: $1686 with: $1698 replace all David Marr wrote: Because it takes too long as i have a vast amount of data. Dave "Bob Phillips" wrote: Why not just use Find & Replace, Ctrl-H? -- HTH Bob "David Marr" <David wrote in message ... 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 . -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment value in a Formula
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 "Bernard Liengme" wrote: 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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "David Marr" <David wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula increment | Excel Worksheet Functions | |||
increment Address formula | Excel Discussion (Misc queries) | |||
increment an index in a formula | Excel Discussion (Misc queries) | |||
Increment formula for time | Excel Discussion (Misc queries) | |||
How do I increment a formula? | Excel Worksheet Functions |