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?

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?

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?

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?