I need a Do Loop to perform the following:

Select Column H
Find "ZZZ Total"
From that Active Cell, move 10 columns to the right and one cell down
Insert Formula "=Active Cell -1"
Loop until Column H cell value = ""

Thanks for the Help!

Mary

dim c as range
for each c in activesheet.range("H:H")

if len(c.value)=0 then exit for
if c.value="ZZZ Total" then
c.offset(1,10).formula = "=" & c.address(false,false) & "-1"
end if

next c

--
Tim Williams
Palo Alto, CA


"Lost in Alabama" wrote in message
...
I need a Do Loop to perform the following:

Select Column H
Find "ZZZ Total"
From that Active Cell, move 10 columns to the right and one cell down
Insert Formula "=Active Cell -1"
Loop until Column H cell value = ""

Thanks for the Help!

Mary

Tim,

Thanks for the quick response. This works except I need the formula to be
the value contained in the cell one row up from the cell where the formula is.

Thanks,

Mary

"Tim Williams" wrote:

dim c as range
for each c in activesheet.range("H:H")

if len(c.value)=0 then exit for
if c.value="ZZZ Total" then
c.offset(1,10).formula = "=" & c.address(false,false) & "-1"
end if

next c

--
Tim Williams
Palo Alto, CA


"Lost in Alabama" wrote in message
...
I need a Do Loop to perform the following:

Select Column H
Find "ZZZ Total"
From that Active Cell, move 10 columns to the right and one cell down
Insert Formula "=Active Cell -1"
Loop until Column H cell value = ""

Thanks for the Help!

Mary

Assume

H15: ZZZ Total
R15: 100
R16: ??????

what do you want entered in R16
what do you want displayed in R16

--
Regards,
Tom Ogilvy


"Lost in Alabama" wrote in message
...
Tim,

Thanks for the quick response. This works except I need the formula to be
the value contained in the cell one row up from the cell where the formula
is.

Thanks,

Mary

"Tim Williams" wrote:

dim c as range
for each c in activesheet.range("H:H")

if len(c.value)=0 then exit for
if c.value="ZZZ Total" then
c.offset(1,10).formula = "=" & c.address(false,false) & "-1"
end if

next c

--
Tim Williams
Palo Alto, CA


"Lost in Alabama" wrote in
message
...
I need a Do Loop to perform the following:

Select Column H
Find "ZZZ Total"
From that Active Cell, move 10 columns to the right and one cell down
Insert Formula "=Active Cell -1"
Loop until Column H cell value = ""

Thanks for the Help!

Mary

Dim Cell As Range

For Each Cell In Range("H:H")
If Cell.Value = "ZZZ Total" Then
Cell.Offset(1, 10).Formula = "=" & Cell.Offset(-1, 0).Address(False,
False)
End If
Next Cell


"Lost in Alabama" wrote:

I need a Do Loop to perform the following:

Select Column H
Find "ZZZ Total"
From that Active Cell, move 10 columns to the right and one cell down
Insert Formula "=Active Cell -1"
Loop until Column H cell value = ""

Thanks for the Help!

Mary

Charile,

Thanks for the quick response. This works except I need the value contained
in the cell found in Column R to be one cell up in Column R.

Thanks,

Mary


"Charlie" wrote:

Dim Cell As Range

For Each Cell In Range("H:H")
If Cell.Value = "ZZZ Total" Then
Cell.Offset(1, 10).Formula = "=" & Cell.Offset(-1, 0).Address(False,
False)
End If
Next Cell


"Lost in Alabama" wrote:

I need a Do Loop to perform the following:

Select Column H
Find "ZZZ Total"
From that Active Cell, move 10 columns to the right and one cell down
Insert Formula "=Active Cell -1"
Loop until Column H cell value = ""

Thanks for the Help!

Mary

Thanks to both you and Tim,

The following works great.


Dim Cell As Range

For Each Cell In Range("H:H")
If Cell.Value = "ZZZTotal" Then
Cell.Offset(1, 10).Formula = "=" & Cell.Offset(0, 10).Address(False, False)
End If
Next Cell

You Guys are GREAT!!

Mary




"Lost in Alabama" wrote:

Charile,

Thanks for the quick response. This works except I need the value contained
in the cell found in Column R to be one cell up in Column R.

Thanks,

Mary


"Charlie" wrote:

Dim Cell As Range

For Each Cell In Range("H:H")
If Cell.Value = "ZZZ Total" Then
Cell.Offset(1, 10).Formula = "=" & Cell.Offset(-1, 0).Address(False,
False)
End If
Next Cell


"Lost in Alabama" wrote:

I need a Do Loop to perform the following:

Select Column H
Find "ZZZ Total"
From that Active Cell, move 10 columns to the right and one cell down
Insert Formula "=Active Cell -1"
Loop until Column H cell value = ""

Thanks for the Help!

Mary

you mean like this?

Cell.Offset(1, 10).Formula = "=" & Cell.Offset(0, 10).Address(False,
False)


"Lost in Alabama" wrote:

Charile,

Thanks for the quick response. This works except I need the value contained
in the cell found in Column R to be one cell up in Column R.

Thanks,

Mary


"Charlie" wrote:

Dim Cell As Range

For Each Cell In Range("H:H")
If Cell.Value = "ZZZ Total" Then
Cell.Offset(1, 10).Formula = "=" & Cell.Offset(-1, 0).Address(False,
False)
End If
Next Cell


"Lost in Alabama" wrote:

I need a Do Loop to perform the following:

Select Column H
Find "ZZZ Total"
From that Active Cell, move 10 columns to the right and one cell down
Insert Formula "=Active Cell -1"
Loop until Column H cell value = ""

Thanks for the Help!

Mary