Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
increment a row reference with addition
Here is what I am trying to do:
1. select a range of cells, which may or may not be continuous 2. change the row number reference in each of the cell's formulas by adding or subtracting to an integer to each reference to a row number i.e. if one of the cells in the range had the formula =H325+H320+H308+H303 I would like to be able to add or subtract an integer to it to get (in the instance the integer was +2) =H327+H322+H310+H305 This operation would need to repeat for each cell in the selected range. I read a few similar posts to try and get this to work, but didn't have any success. A solution would be greatly appreciated ! Thanks, Joshua |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
increment a row reference with addition
with very minimal testing, this worked for me
Sub FormulaCvt(rng As Range, rowoffst As Long) Dim cell As Range, cell1 As Range For Each cell In rng s = cell.Formula On Error Resume Next Set cell1 = cell.Offset(-1 * rowoffst, 0) On Error GoTo 0 If Not cell1 Is Nothing Then s1 = Application.ConvertFormula(s, xlA1, _ xlR1C1, , ActiveCell.Offset(-1 * rowoffst, 0)) cell.FormulaR1C1 = s1 Else MsgBox "Can't convert" Exit Sub End If Next End Sub Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub --------------- Results in the immediate window we =H325+H320+H308+H303 =H327+H322+H310+H305 -- Regards, Tom Ogilvy "cass calculator" wrote: Here is what I am trying to do: 1. select a range of cells, which may or may not be continuous 2. change the row number reference in each of the cell's formulas by adding or subtracting to an integer to each reference to a row number i.e. if one of the cells in the range had the formula =H325+H320+H308+H303 I would like to be able to add or subtract an integer to it to get (in the instance the integer was +2) =H327+H322+H310+H305 This operation would need to repeat for each cell in the selected range. I read a few similar posts to try and get this to work, but didn't have any success. A solution would be greatly appreciated ! Thanks, Joshua |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
increment a row reference with addition
On Jan 31, 11:52 am, Tom Ogilvy
wrote: with very minimal testing, this worked for me Sub FormulaCvt(rng As Range, rowoffst As Long) Dim cell As Range, cell1 As Range For Each cell In rng s = cell.Formula On Error Resume Next Set cell1 = cell.Offset(-1 * rowoffst, 0) On Error GoTo 0 If Not cell1 Is Nothing Then s1 = Application.ConvertFormula(s, xlA1, _ xlR1C1, , ActiveCell.Offset(-1 * rowoffst, 0)) cell.FormulaR1C1 = s1 Else MsgBox "Can't convert" Exit Sub End If Next End Sub Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub --------------- Results in the immediate window we =H325+H320+H308+H303 =H327+H322+H310+H305 -- Regards, Tom Ogilvy "cass calculator" wrote: Here is what I am trying to do: 1. select a range of cells, which may or may not be continuous 2. change the row number reference in each of the cell's formulas by adding or subtracting to an integer to each reference to a row number i.e. if one of the cells in the range had the formula =H325+H320+H308+H303 I would like to be able to add or subtract an integer to it to get (in the instance the integer was +2) =H327+H322+H310+H305 This operation would need to repeat for each cell in the selected range. I read a few similar posts to try and get this to work, but didn't have any success. A solution would be greatly appreciated ! Thanks, Joshua Thank you very much Tom. This is probably a stupid question, but how do I pass the 2 arguments (rng and rowoffst) through the sub? I tried to just run it by selecting my range and doing Alt + F8 and typing the name of the sub. I also tried setting the rowoffst variable equal to a user input box using rowoffst = InputBox("Input Row Offset", "Input Row Offset", 0) but I got the error "Argument Not Optional" in both cases. Sorry, I'm somewhat of a newbie to this and realize this is probably a fundamental question. Thanks so much for all your help! Joshua |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
increment a row reference with addition
I included:
Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub which shows you how. FormulaCvt ActiveCell, 2 Activecell and or 2 or both can be replaced with variables Sub doconversion() Dim rng as Range, ofst as Long On error resume Next set rng = Application.InputBox("Select a range with the mouse",Type:=8) On error goto 0 if rng is nothing then msgbox "no range selected" exit sub End if res = Inputbox("enter the integer offset, ex: 2") if isnumeric(res) then ofst = int(res) else msgbox "Bad offset value" exit sub end if FormulaCvt rng, ofst end Sub -- Regards, Tom Ogilvy "cass calculator" wrote: On Jan 31, 11:52 am, Tom Ogilvy wrote: with very minimal testing, this worked for me Sub FormulaCvt(rng As Range, rowoffst As Long) Dim cell As Range, cell1 As Range For Each cell In rng s = cell.Formula On Error Resume Next Set cell1 = cell.Offset(-1 * rowoffst, 0) On Error GoTo 0 If Not cell1 Is Nothing Then s1 = Application.ConvertFormula(s, xlA1, _ xlR1C1, , ActiveCell.Offset(-1 * rowoffst, 0)) cell.FormulaR1C1 = s1 Else MsgBox "Can't convert" Exit Sub End If Next End Sub Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub --------------- Results in the immediate window we =H325+H320+H308+H303 =H327+H322+H310+H305 -- Regards, Tom Ogilvy "cass calculator" wrote: Here is what I am trying to do: 1. select a range of cells, which may or may not be continuous 2. change the row number reference in each of the cell's formulas by adding or subtracting to an integer to each reference to a row number i.e. if one of the cells in the range had the formula =H325+H320+H308+H303 I would like to be able to add or subtract an integer to it to get (in the instance the integer was +2) =H327+H322+H310+H305 This operation would need to repeat for each cell in the selected range. I read a few similar posts to try and get this to work, but didn't have any success. A solution would be greatly appreciated ! Thanks, Joshua Thank you very much Tom. This is probably a stupid question, but how do I pass the 2 arguments (rng and rowoffst) through the sub? I tried to just run it by selecting my range and doing Alt + F8 and typing the name of the sub. I also tried setting the rowoffst variable equal to a user input box using rowoffst = InputBox("Input Row Offset", "Input Row Offset", 0) but I got the error "Argument Not Optional" in both cases. Sorry, I'm somewhat of a newbie to this and realize this is probably a fundamental question. Thanks so much for all your help! Joshua |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
increment a row reference with addition
On Jan 31, 12:44 pm, Tom Ogilvy
wrote: I included: Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub which shows you how. FormulaCvt ActiveCell, 2 Activecell and or 2 or both can be replaced with variables Sub doconversion() Dim rng as Range, ofst as Long On error resume Next set rng = Application.InputBox("Select a range with the mouse",Type:=8) On error goto 0 if rng is nothing then msgbox "no range selected" exit sub End if res = Inputbox("enter the integer offset, ex: 2") if isnumeric(res) then ofst = int(res) else msgbox "Bad offset value" exit sub end if FormulaCvt rng, ofst end Sub -- Regards, Tom Ogilvy "cass calculator" wrote: On Jan 31, 11:52 am, Tom Ogilvy wrote: with very minimal testing, this worked for me Sub FormulaCvt(rng As Range, rowoffst As Long) Dim cell As Range, cell1 As Range For Each cell In rng s = cell.Formula On Error Resume Next Set cell1 = cell.Offset(-1 * rowoffst, 0) On Error GoTo 0 If Not cell1 Is Nothing Then s1 = Application.ConvertFormula(s, xlA1, _ xlR1C1, , ActiveCell.Offset(-1 * rowoffst, 0)) cell.FormulaR1C1 = s1 Else MsgBox "Can't convert" Exit Sub End If Next End Sub Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub --------------- Results in the immediate window we =H325+H320+H308+H303 =H327+H322+H310+H305 -- Regards, Tom Ogilvy "cass calculator" wrote: Here is what I am trying to do: 1. select a range of cells, which may or may not be continuous 2. change the row number reference in each of the cell's formulas by adding or subtracting to an integer to each reference to a row number i.e. if one of the cells in the range had the formula =H325+H320+H308+H303 I would like to be able to add or subtract an integer to it to get (in the instance the integer was +2) =H327+H322+H310+H305 This operation would need to repeat for each cell in the selected range. I read a few similar posts to try and get this to work, but didn't have any success. A solution would be greatly appreciated ! Thanks, Joshua Thank you very much Tom. This is probably a stupid question, but how do I pass the 2 arguments (rng and rowoffst) through the sub? I tried to just run it by selecting my range and doing Alt + F8 and typing the name of the sub. I also tried setting the rowoffst variable equal to a user input box using rowoffst = InputBox("Input Row Offset", "Input Row Offset", 0) but I got the error "Argument Not Optional" in both cases. Sorry, I'm somewhat of a newbie to this and realize this is probably a fundamental question. Thanks so much for all your help! Joshua Thanks Tom. It looks like it works for the first cell in the range, but for the subsequent cells in the range it adjusts the column reference in the formula too. I.E. I tried it with three cells in the range, all in the same row, using two as the rowoffst variable. The first cell in the in the range converted perfectly. The second and third cells' formulas converted the row number correctly, but the second cell in the range offset the column reference by 1 and the third cell in the range offset the column reference by 2 I started with this: =A1+A2+A3 =B1+B2+B3 =C1+C2+C3 Function changed it to: =A3+A4+A5 =C3+C4+C5 =E3+E4+E5 Rather than: =A3+A4+A5 =B3+B4+B5 =C3+C4+C5 Thanks so much for all your help with this! Joshua |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
increment a row reference with addition
I had tested it initially using the ActiveCell and then generalized it. I
didn't get all references cleaned up. Sub FormulaCvt(rng As Range, rowoffst As Long) Dim cell As Range, cell1 As Range For Each cell In rng s = cell.Formula On Error Resume Next Set cell1 = cell.Offset(-1 * rowoffst, 0) On Error GoTo 0 If Not cell1 Is Nothing Then s1 = Application.ConvertFormula(s, xlA1, _ xlR1C1, , cell.Offset(-1 * rowoffst, 0)) cell.FormulaR1C1 = s1 Else MsgBox "Can't convert" Exit Sub End If Next End Sub worked for me with a similar test as you described. -- Regards, Tom Ogilvy "cass calculator" wrote in message oups.com... On Jan 31, 12:44 pm, Tom Ogilvy wrote: I included: Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub which shows you how. FormulaCvt ActiveCell, 2 Activecell and or 2 or both can be replaced with variables Sub doconversion() Dim rng as Range, ofst as Long On error resume Next set rng = Application.InputBox("Select a range with the mouse",Type:=8) On error goto 0 if rng is nothing then msgbox "no range selected" exit sub End if res = Inputbox("enter the integer offset, ex: 2") if isnumeric(res) then ofst = int(res) else msgbox "Bad offset value" exit sub end if FormulaCvt rng, ofst end Sub -- Regards, Tom Ogilvy "cass calculator" wrote: On Jan 31, 11:52 am, Tom Ogilvy wrote: with very minimal testing, this worked for me Sub FormulaCvt(rng As Range, rowoffst As Long) Dim cell As Range, cell1 As Range For Each cell In rng s = cell.Formula On Error Resume Next Set cell1 = cell.Offset(-1 * rowoffst, 0) On Error GoTo 0 If Not cell1 Is Nothing Then s1 = Application.ConvertFormula(s, xlA1, _ xlR1C1, , ActiveCell.Offset(-1 * rowoffst, 0)) cell.FormulaR1C1 = s1 Else MsgBox "Can't convert" Exit Sub End If Next End Sub Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub --------------- Results in the immediate window we =H325+H320+H308+H303 =H327+H322+H310+H305 -- Regards, Tom Ogilvy "cass calculator" wrote: Here is what I am trying to do: 1. select a range of cells, which may or may not be continuous 2. change the row number reference in each of the cell's formulas by adding or subtracting to an integer to each reference to a row number i.e. if one of the cells in the range had the formula =H325+H320+H308+H303 I would like to be able to add or subtract an integer to it to get (in the instance the integer was +2) =H327+H322+H310+H305 This operation would need to repeat for each cell in the selected range. I read a few similar posts to try and get this to work, but didn't have any success. A solution would be greatly appreciated ! Thanks, Joshua Thank you very much Tom. This is probably a stupid question, but how do I pass the 2 arguments (rng and rowoffst) through the sub? I tried to just run it by selecting my range and doing Alt + F8 and typing the name of the sub. I also tried setting the rowoffst variable equal to a user input box using rowoffst = InputBox("Input Row Offset", "Input Row Offset", 0) but I got the error "Argument Not Optional" in both cases. Sorry, I'm somewhat of a newbie to this and realize this is probably a fundamental question. Thanks so much for all your help! Joshua Thanks Tom. It looks like it works for the first cell in the range, but for the subsequent cells in the range it adjusts the column reference in the formula too. I.E. I tried it with three cells in the range, all in the same row, using two as the rowoffst variable. The first cell in the in the range converted perfectly. The second and third cells' formulas converted the row number correctly, but the second cell in the range offset the column reference by 1 and the third cell in the range offset the column reference by 2 I started with this: =A1+A2+A3 =B1+B2+B3 =C1+C2+C3 Function changed it to: =A3+A4+A5 =C3+C4+C5 =E3+E4+E5 Rather than: =A3+A4+A5 =B3+B4+B5 =C3+C4+C5 Thanks so much for all your help with this! Joshua |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
increment a row reference with addition
On Jan 31, 11:08 pm, "Tom Ogilvy" wrote:
I had tested it initially using the ActiveCell and then generalized it. I didn't get all references cleaned up. Sub FormulaCvt(rng As Range, rowoffst As Long) Dim cell As Range, cell1 As Range For Each cell In rng s = cell.Formula On Error Resume Next Set cell1 = cell.Offset(-1 * rowoffst, 0) On Error GoTo 0 If Not cell1 Is Nothing Then s1 = Application.ConvertFormula(s, xlA1, _ xlR1C1, , cell.Offset(-1 * rowoffst, 0)) cell.FormulaR1C1 = s1 Else MsgBox "Can't convert" Exit Sub End If Next End Sub worked for me with a similar test as you described. -- Regards, Tom Ogilvy "cass calculator" wrote in message oups.com... On Jan 31, 12:44 pm, Tom Ogilvy wrote: I included: Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub which shows you how. FormulaCvt ActiveCell, 2 Activecell and or 2 or both can be replaced with variables Sub doconversion() Dim rng as Range, ofst as Long On error resume Next set rng = Application.InputBox("Select a range with the mouse",Type:=8) On error goto 0 if rng is nothing then msgbox "no range selected" exit sub End if res = Inputbox("enter the integer offset, ex: 2") if isnumeric(res) then ofst = int(res) else msgbox "Bad offset value" exit sub end if FormulaCvt rng, ofst end Sub -- Regards, Tom Ogilvy "cass calculator" wrote: On Jan 31, 11:52 am, Tom Ogilvy wrote: with very minimal testing, this worked for me Sub FormulaCvt(rng As Range, rowoffst As Long) Dim cell As Range, cell1 As Range For Each cell In rng s = cell.Formula On Error Resume Next Set cell1 = cell.Offset(-1 * rowoffst, 0) On Error GoTo 0 If Not cell1 Is Nothing Then s1 = Application.ConvertFormula(s, xlA1, _ xlR1C1, , ActiveCell.Offset(-1 * rowoffst, 0)) cell.FormulaR1C1 = s1 Else MsgBox "Can't convert" Exit Sub End If Next End Sub Sub Tester1() ActiveCell.Formula = "=H325+H320+H308+H303" Debug.Print ActiveCell.Formula FormulaCvt ActiveCell, 2 Debug.Print ActiveCell.Formula End Sub --------------- Results in the immediate window we =H325+H320+H308+H303 =H327+H322+H310+H305 -- Regards, Tom Ogilvy "cass calculator" wrote: Here is what I am trying to do: 1. select a range of cells, which may or may not be continuous 2. change the row number reference in each of the cell's formulas by adding or subtracting to an integer to each reference to a row number i.e. if one of the cells in the range had the formula =H325+H320+H308+H303 I would like to be able to add or subtract an integer to it to get (in the instance the integer was +2) =H327+H322+H310+H305 This operation would need to repeat for each cell in the selected range. I read a few similar posts to try and get this to work, but didn't have any success. A solution would be greatly appreciated ! Thanks, Joshua Thank you very much Tom. This is probably a stupid question, but how do I pass the 2 arguments (rng and rowoffst) through the sub? I tried to just run it by selecting my range and doing Alt + F8 and typing the name of the sub. I also tried setting the rowoffst variable equal to a user input box using rowoffst = InputBox("Input Row Offset", "Input Row Offset", 0) but I got the error "Argument Not Optional" in both cases. Sorry, I'm somewhat of a newbie to this and realize this is probably a fundamental question. Thanks so much for all your help! Joshua Thanks Tom. It looks like it works for the first cell in the range, but for the subsequent cells in the range it adjusts the column reference in the formula too. I.E. I tried it with three cells in the range, all in the same row, using two as the rowoffst variable. The first cell in the in the range converted perfectly. The second and third cells' formulas converted the row number correctly, but the second cell in the range offset the column reference by 1 and the third cell in the range offset the column reference by 2 I started with this: =A1+A2+A3 =B1+B2+B3 =C1+C2+C3 Function changed it to: =A3+A4+A5 =C3+C4+C5 =E3+E4+E5 Rather than: =A3+A4+A5 =B3+B4+B5 =C3+C4+C5 Thanks so much for all your help with this! Joshua Works perfectly! Thanks so much for your help, you are the best! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to increment a cell reference in a row | Excel Discussion (Misc queries) | |||
How do I get a worksheet reference to increment when copied | Excel Worksheet Functions | |||
Increment cell reference | Excel Discussion (Misc queries) | |||
Relative reference autofill increment other than +1 | Excel Discussion (Misc queries) | |||
Row reference increment but preserve column reference | Excel Worksheet Functions |