ExcelBanter - R[1]C[1] with a variable

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - R[1]C[1] with a variable (https://www.excelbanter.com/excel-programming/397175-r%5B1%5Dc%5B1%5D-variable.html)

R[1]C[1] with a variable

I am trying to automatically validate some data and am trying to do a sum on
a variable range but it doesn't work!

ActiveCell.FormulaR1C1 = "=SUM(R[-myvariable]C:R[-1]C)"

where myvariable is the number of rows to sum

Can anyone help?

Thanks, Dean

Code below: (probably a very inefficient and long winded way of doing it but
as long as it works I'll be happy!)

Sub Loop1()

Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
Range("I3").Select
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],""Yes"",""No"")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Range("I1").Select
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("I1").Select

Range("I3").Select
rdlA = 0
Do
If ActiveCell.FormulaR1C1 = "Yes" Then
rdlA = rdlA + 1
ElseIf ActiveCell.FormulaR1C1 = "No" Then
Selection.EntireRow.Insert
If rdlA 1 Then
ActiveCell.Offset(0, 4).Select
'error here
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(1, 7).Select
Selection.EntireRow.Insert
rdlA = 0
Else
End If

Else
MsgBox "Error"
End If
ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

Range("I2").Select
ActiveCell.FormulaR1C1 = "Yes"
Range("I1").Select

End Sub

R[1]C[1] with a variable

ActiveCell.FormulaR1C1 = "=SUM(R[-" & myvariable & "]C:R[-1]C)"

Dean@ERYC wrote:

I am trying to automatically validate some data and am trying to do a sum on
a variable range but it doesn't work!

ActiveCell.FormulaR1C1 = "=SUM(R[-myvariable]C:R[-1]C)"

where myvariable is the number of rows to sum

Can anyone help?

Thanks, Dean

Code below: (probably a very inefficient and long winded way of doing it but
as long as it works I'll be happy!)

Sub Loop1()

Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
Range("I3").Select
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],""Yes"",""No"")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Range("I1").Select
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("I1").Select

Range("I3").Select
rdlA = 0
Do
If ActiveCell.FormulaR1C1 = "Yes" Then
rdlA = rdlA + 1
ElseIf ActiveCell.FormulaR1C1 = "No" Then
Selection.EntireRow.Insert
If rdlA 1 Then
ActiveCell.Offset(0, 4).Select
'error here
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(1, 7).Select
Selection.EntireRow.Insert
rdlA = 0
Else
End If

Else
MsgBox "Error"
End If
ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

Range("I2").Select
ActiveCell.FormulaR1C1 = "Yes"
Range("I1").Select

End Sub

--

Dave Peterson

R[1]C[1] with a variable

ActiveCell.FormulaR1C1 = "=SUM(R[-" & myvariable & "]C:R[-1]C)"

--
Regards,
Tom Ogilvy

"Dean@ERYC" wrote:

I am trying to automatically validate some data and am trying to do a sum on
a variable range but it doesn't work!

ActiveCell.FormulaR1C1 = "=SUM(R[-myvariable]C:R[-1]C)"

where myvariable is the number of rows to sum

Can anyone help?

Thanks, Dean

Code below: (probably a very inefficient and long winded way of doing it but
as long as it works I'll be happy!)

Sub Loop1()

Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
Range("I3").Select
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],""Yes"",""No"")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Range("I1").Select
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("I1").Select

Range("I3").Select
rdlA = 0
Do
If ActiveCell.FormulaR1C1 = "Yes" Then
rdlA = rdlA + 1
ElseIf ActiveCell.FormulaR1C1 = "No" Then
Selection.EntireRow.Insert
If rdlA 1 Then
ActiveCell.Offset(0, 4).Select
'error here
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-rdlA]C:R[-1]C)"
ActiveCell.Offset(1, 7).Select
Selection.EntireRow.Insert
rdlA = 0
Else
End If

Else
MsgBox "Error"
End If
ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

Range("I2").Select
ActiveCell.FormulaR1C1 = "Yes"
Range("I1").Select

End Sub