Reference columns using For each r in currentregion
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)
"RominallL" wrote in message
...
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" wrote:
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?
|