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

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

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

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

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

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

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!