For Each r In ActiveCell.CurrentRegion
If r.Value = 6 Then
r.Offset(0, 6).Value = "=" & r.Value & "/6"
ElseIf r.Value = "" Then
r.Resize(, 27).Value = 3
Else
r.Resize(, 53).Formula = "=SUM(" & r.Offset(-1, 0).Address & ":" &
r.Offset(3, 0).Address & ")"
End If
Next r


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"RominallL" wrote in message
...
I've tried using this for an if then type scenario but I get hung up
trying
to reference the columns.
Something like

for each r in currentregion
If cell("A"+R+"").value = 6 then
cell( ---A + 6 columns-----).value = '=sum(A"+r+" / 6)'
Elseif cell("A"+r+"").value = "" then
cells(A"+r+":AA"+r+"").value = 3
Else
cells(A"+r+":BA"+r+"").value = '=sum(A" -------r minus 1
row--------" + A" + ---------r plus 3 rows-------")
end if
next r

There's more formula stuff that goes into it, but I can't seem to get the
row/column reference correct.

I will never know the exact column or row references prior to the begining
of code exectution. Right now I'm doing a select current region, count
rows
and columns and then doing some really combersome variable declarations.
Any
way to make it tighter?