increment a row reference with addition

  • Thread starter Thread starter cass calculator
  • Start date Start date
C

cass calculator

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
 
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
 
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
Click to expand...

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
 
I included:
Sub Tester1()
ActiveCell.Formula = "=H325+H320+H308+H303"
Debug.Print ActiveCell.Formula
FormulaCvt ActiveCell, 2
Debug.Print ActiveCell.Formula
End Sub
Click to expand...


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
 
I included:


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
Click to expand...


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
 
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.
 
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.
Click to expand...

Works perfectly! Thanks so much for your help, you are the best!
 
Back
Top