Cells(r, c) syntax and horizontal alignment

I have a vb macro examining cells in some columns for all rows in a worksheet.
I want to left and right align selected cells based on testing.
The code is working fine to test and move values, and I know the
value "trigger" for the L and R alignment.

I don't know how to "translate" the documentation help example below
into the syntax I'm using to address the cells in the program.
The macro will be run for worksheets that have different names,
but have the same row and column "definitions."

Help? Thanks.

Help example:
Worksheets("Sheet1").Range("A1:A5").HorizontalAlignment = xlLeft

(or right)
My code's "essence":
Do until cells(row, 4)='9999999' 'end marker
row = row + 1
col = "whatever"

If cells(row, col) "condition" then cells(row, col) = new value

now, If different var test then "right align the above cell"

Loop
--
Neal Z

try this to find 9999 in column D

with Worksheets("Sheet1")
X = . columns(4).Find(9999).row
..Range("A1:A"&x).HorizontalAlignment = xlLeft
end with
--
Don Guillett
SalesAid Software
donaldb@281.com
"Neal Zimm" <nealzimm@yahoo.com> wrote in message
news:4BF07FB8-1D89-4330-B228-ECE79818DA9D@microsoft.com...
> I have a vb macro examining cells in some columns for all rows in a
worksheet.
> I want to left and right align selected cells based on testing.
> The code is working fine to test and move values, and I know the
> value "trigger" for the L and R alignment.
>
> I don't know how to "translate" the documentation help example below
> into the syntax I'm using to address the cells in the program.
> The macro will be run for worksheets that have different names,
> but have the same row and column "definitions."
>
> Help? Thanks.
>
> Help example:
> Worksheets("Sheet1").Range("A1:A5").HorizontalAlignment = xlLeft
>
> (or right)
> My code's "essence":
> Do until cells(row, 4)='9999999' 'end marker
> row = row + 1
> col = "whatever"
>
> If cells(row, col) "condition" then cells(row, col) = new value
>
> now, If different var test then "right align the above cell"
>
> Loop
> --
> Neal Z

Thanks, we're "halfway home", but more help is needed, please.
there are 3 items below.

1. The "9's search" you provided, thanks, but it's not my problem, though
your method will be helpful to me.

2. Thanks, I never realized you could use the &x syntax as part of the
range statement. I am self learning vb but have programmed in other
languages. I am currently using cells(row, col)=..... successfully where I
vary the value of the row and col vars, so in my example,

do until.....
row = row + 1 ' let's say row is now 28
x=row
more code
range("B"&x) is the equivalent of cells(28, 2)
more code
loop

Should this work to vary the column so to align a single cell will be?


let colvalue = "B"
....range(&colvalue&x).... ?

3. How do you vary, or get the value of the "Sheet1" part of your answer? I
have not yet been able to find an example to vary the "Sheet1" part of your
phrase: with Worksheets("Sheet1"). My code is called as part of a longer
macro which contains both "key recorded" macro pieces and called macros. My
application is that delivery managers have workbooks, where each sheet
represents a route. It's impossible for me to know how the route sheets are
named so to use your example, I also need away to "get" and vary the sheet
name. Thanks again.
Neal Z

"Don Guillett" wrote:

>
> try this to find 9999 in column D
>
> with Worksheets("Sheet1")
> X = . columns(4).Find(9999).row
> ..Range("A1:A"&x).HorizontalAlignment = xlLeft
> end with
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Neal Zimm" <nealzimm@yahoo.com> wrote in message
> news:4BF07FB8-1D89-4330-B228-ECE79818DA9D@microsoft.com...
> > I have a vb macro examining cells in some columns for all rows in a
> worksheet.
> > I want to left and right align selected cells based on testing.
> > The code is working fine to test and move values, and I know the
> > value "trigger" for the L and R alignment.
> >
> > I don't know how to "translate" the documentation help example below
> > into the syntax I'm using to address the cells in the program.
> > The macro will be run for worksheets that have different names,
> > but have the same row and column "definitions."
> >
> > Help? Thanks.
> >
> > Help example:
> > Worksheets("Sheet1").Range("A1:A5").HorizontalAlignment = xlLeft
> >
> > (or right)
> > My code's "essence":
> > Do until cells(row, 4)='9999999' 'end marker
> > row = row + 1
> > col = "whatever"
> >
> > If cells(row, col) "condition" then cells(row, col) = new value
> >
> > now, If different var test then "right align the above cell"
> >
> > Loop
> > --
> > Neal Z
>
>
>

3. If you are at the sheet you want to use then use

with activesheet

--
Don Guillett
SalesAid Software
donaldb@281.com
"Neal Zimm" <nealzimm@yahoo.com> wrote in message
news:93F3A652-FC25-4524-8C93-E7C2FEE13D26@microsoft.com...
> Thanks, we're "halfway home", but more help is needed, please.
> there are 3 items below.
>
> 1. The "9's search" you provided, thanks, but it's not my problem, though
> your method will be helpful to me.
>
> 2. Thanks, I never realized you could use the &x syntax as part of the
> range statement. I am self learning vb but have programmed in other
> languages. I am currently using cells(row, col)=..... successfully where
I
> vary the value of the row and col vars, so in my example,
>
> do until.....
> row = row + 1 ' let's say row is now 28
> x=row
> more code
> range("B"&x) is the equivalent of cells(28, 2)
> more code
> loop
>
> Should this work to vary the column so to align a single cell will be?
>
>
> let colvalue = "B"
> ...range(&colvalue&x).... ?
>
> 3. How do you vary, or get the value of the "Sheet1" part of your answer?
I
> have not yet been able to find an example to vary the "Sheet1" part of
your
> phrase: with Worksheets("Sheet1"). My code is called as part of a
longer
> macro which contains both "key recorded" macro pieces and called macros.
My
> application is that delivery managers have workbooks, where each sheet
> represents a route. It's impossible for me to know how the route sheets
are
> named so to use your example, I also need away to "get" and vary the sheet
> name. Thanks again.
> Neal Z
>
> "Don Guillett" wrote:
>
> >
> > try this to find 9999 in column D
> >
> > with Worksheets("Sheet1")
> > X = . columns(4).Find(9999).row
> > ..Range("A1:A"&x).HorizontalAlignment = xlLeft
> > end with
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "Neal Zimm" <nealzimm@yahoo.com> wrote in message
> > news:4BF07FB8-1D89-4330-B228-ECE79818DA9D@microsoft.com...
> > > I have a vb macro examining cells in some columns for all rows in a
> > worksheet.
> > > I want to left and right align selected cells based on testing.
> > > The code is working fine to test and move values, and I know the
> > > value "trigger" for the L and R alignment.
> > >
> > > I don't know how to "translate" the documentation help example below
> > > into the syntax I'm using to address the cells in the program.
> > > The macro will be run for worksheets that have different names,
> > > but have the same row and column "definitions."
> > >
> > > Help? Thanks.
> > >
> > > Help example:
> > > Worksheets("Sheet1").Range("A1:A5").HorizontalAlignment = xlLeft
> > >
> > > (or right)
> > > My code's "essence":
> > > Do until cells(row, 4)='9999999' 'end marker
> > > row = row + 1
> > > col = "whatever"
> > >
> > > If cells(row, col) "condition" then cells(row, col) = new value
> > >
> > > now, If different var test then "right align the above cell"
> > >
> > > Loop
> > > --
> > > Neal Z
> >
> >
> >