Cells(r, c) syntax and horizontal alignment

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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]

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

with activesheet