"Loop until" syntax question

[Fastbike]

Hi,
I am after help for the following:

Range("TotalCentralregion").Select ' Range is a single cell
Selection.Offset(1, 0).Select
Selection.Formula = "=(" & ActiveCell.Offset(0, -2).Address & " /EastTotal)"

What I need to do is loop this formula down a column until it gets to a single cell that has been named "TotalEastRegion". (Formula has to include "TotaleastRegion).

Thanks for the help in advance.

Fastbike

[Jim Rech]

This really isn't a need to do any looping (or selecting):

Sub demo()
With Range(Range("TotalCentralRegion").Offset(1), "TotalEastRegion")
.FormulaR1C1 = "=RC[-2]/EastTotal"
End With
End Sub

--
Jim
"Fastbike" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I am after help for the following:
>
> Range("TotalCentralregion").Select ' Range is a single cell
> Selection.Offset(1, 0).Select
> Selection.Formula = "=(" & ActiveCell.Offset(0, -2).Address & "
> /EastTotal)"
>
> What I need to do is loop this formula down a column until it gets to a
> single cell that has been named "TotalEastRegion". (Formula has to
> include "TotaleastRegion).
>
> Thanks for the help in advance.
>
> Fastbike
>
>
> --
> Fastbike
> ------------------------------------------------------------------------
> Fastbike's Profile:
> http://www.excelforum.com/member.php...o&userid=26701
> View this thread: http://www.excelforum.com/showthread...hreadid=401530
>

[Jim Thomlinson]

The easiest way is to create a Range which is all of the cells between the
two named ranges and then ust traverse those cells... Kind of like this

dim rngToSearch as Range
dim rngCurrent as range

set rngToSearch = range(Range("TotalCentralregion"), Range("TotalEastregion"))
for each rngCurrent in rngToSearch
'rngCurrent is just like the active cell
rngCurrent.Formula = "=(" & rngCurrent.Offset(0, -2).Address & "/EastTotal)"

next rngCurrent
--
HTH...

Jim Thomlinson


"Fastbike" wrote:

>
> Hi,
> I am after help for the following:
>
> Range("TotalCentralregion").Select ' Range is a single cell
> Selection.Offset(1, 0).Select
> Selection.Formula = "=(" & ActiveCell.Offset(0, -2).Address & "
> /EastTotal)"
>
> What I need to do is loop this formula down a column until it gets to a
> single cell that has been named "TotalEastRegion". (Formula has to
> include "TotaleastRegion).
>
> Thanks for the help in advance.
>
> Fastbike
>
>
> --
> Fastbike
> ------------------------------------------------------------------------
> Fastbike's Profile: http://www.excelforum.com/member.php...o&userid=26701
> View this thread: http://www.excelforum.com/showthread...hreadid=401530
>
>

[Bob Phillips]

Range("TotalCentralregion").select
Do
Selection.(1, 0).Formula = "=(" & ActiveCell.Offset(0, -2).Address &
"/EastTotal)"
ACtivecell.Offset(1.0).select
Loop Until Activecell.value like "*TotalEastRegion*"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fastbike" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I am after help for the following:
>
> Range("TotalCentralregion").Select ' Range is a single cell
> Selection.Offset(1, 0).Select
> Selection.Formula = "=(" & ActiveCell.Offset(0, -2).Address & "
> /EastTotal)"
>
> What I need to do is loop this formula down a column until it gets to a
> single cell that has been named "TotalEastRegion". (Formula has to
> include "TotaleastRegion).
>
> Thanks for the help in advance.
>
> Fastbike
>
>
> --
> Fastbike
> ------------------------------------------------------------------------
> Fastbike's Profile:
http://www.excelforum.com/member.php...o&userid=26701
> View this thread: http://www.excelforum.com/showthread...hreadid=401530
>

[Fastbike]

Hi,
thanks for all the replies. Much appreciated.