R[1]C[1] with a variable

D

Dean@ERYC

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
 
D

Dave Peterson

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



Dean@ERYC said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create Detail Sheets from Pivot Table 1
Insert columns and values programmatically with IF statement 9
works in 2007 but not in 2010 0
Cleaner coding to repeat process 7
criteriarange not equal to 4
how to use Solver in Excel VBA 1
Totals An Easier Way?? 5
changing range in recorded macro 1

Top