Reference columns using For each r in currentregion

[Guest]

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?

 

[Bob Phillips]

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)

 

[Guest]

THANKS!!!

So it starts the active cell at the first column????

I ask because sometimes the current region might start on different columns.

 

[Bob Phillips]

It starts at the first cell in the current region surrounding the
activecell. That could be a row or column before the activecell.

--
---
HTH

Bob

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