Column letter reference as number reference
 

I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)

Column letter reference as number reference
 

The code below will cycle through all the cells in the range you have
described and you can put your code in the middle. AZ equates to 52.

Sub checkcells()
For y = 1 To 57
For x = 1 To 52
check what you want to check for here
Next
Next
End Sub

"mcphc" wrote:

I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)

Column letter reference as number reference
 

Something like:

Sub test()
Dim a
For Each a In Range("print_Area")
MsgBox a
Next
End Sub

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mcphc" wrote in message ...
|I use the below to get the vertical edge of the print area.
|
| Range("Print_Area").Address
|
| The output of this is something like "$A$:1$AZ$57".
|
| I would like to run a macro that checks each cell in a row from column "A"
| to (as in the above) column "AZ"
|
| I would like to use something like
|
| For col = 1 to 52
|
| How do you represent AZ as 52? Or is there a better way of doing this? (I'm
| sure there is)

Column letter reference as number reference
 

public sub mcphc()
dim r as integer, c as integer
' rows 1 to 57
for r = 1 to 57
' columns A to AZ
for c = 1 to 52
' Print value of cell in immediate window
debug.print cells(r,c)
next c
next r
end sub

Cheers,
Jason Lepack

On Jan 30, 7:08 am, mcphc wrote:
I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)

Column letter reference as number reference
 

Thanks for the help guys but not quiet what I was looking for.

The problem is the print area is always different so it could be

For col= 1 to 52

or

For col = 1 to 30

I need something like

For col= 1 to PrintAreaEdge

where PrintAreaEdge is the last vertical column number (ie 52 not letter
"AZ") in the print area.

Using the below

For Each a In Range("print_Area")
MsgBox a
Next

is close but I actually only want to search a few columns within the print
area
ie

For col = 1 to PrintAreaEdge - 5

Thanks

"Jason Lepack" wrote:

public sub mcphc()
dim r as integer, c as integer
' rows 1 to 57
for r = 1 to 57
' columns A to AZ
for c = 1 to 52
' Print value of cell in immediate window
debug.print cells(r,c)
next c
next r
end sub

Cheers,
Jason Lepack

On Jan 30, 7:08 am, mcphc wrote:
I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)

Column letter reference as number reference
 

for col = 1 to range("print_area").column

On Jan 30, 8:23 am, mcphc wrote:
Thanks for the help guys but not quiet what I was looking for.

The problem is the print area is always different so it could be

For col= 1 to 52

or

For col = 1 to 30

I need something like

For col= 1 to PrintAreaEdge

where PrintAreaEdge is the last vertical column number (ie 52 not letter
"AZ") in the print area.

Using the below

For Each a In Range("print_Area")
MsgBox a
Next

is close but I actually only want to search a few columns within the print
area
ie

For col = 1 to PrintAreaEdge - 5

Thanks



"Jason Lepack" wrote:
public sub mcphc()
dim r as integer, c as integer
' rows 1 to 57
for r = 1 to 57
' columns A to AZ
for c = 1 to 52
' Print value of cell in immediate window
debug.print cells(r,c)
next c
next r
end sub

Cheers,
Jason Lepack

On Jan 30, 7:08 am, mcphc wrote:
I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)- Hide quoted text -- Show quoted text -

Column letter reference as number reference
 

If you use Niek Otten's solution then it will select your cells wherever the
print range is even if it is moved

"mcphc" wrote:

Thanks for the help guys but not quiet what I was looking for.

The problem is the print area is always different so it could be

For col= 1 to 52

or

For col = 1 to 30

I need something like

For col= 1 to PrintAreaEdge

where PrintAreaEdge is the last vertical column number (ie 52 not letter
"AZ") in the print area.

Using the below

For Each a In Range("print_Area")
MsgBox a
Next

is close but I actually only want to search a few columns within the print
area
ie

For col = 1 to PrintAreaEdge - 5

Thanks

"Jason Lepack" wrote:

public sub mcphc()
dim r as integer, c as integer
' rows 1 to 57
for r = 1 to 57
' columns A to AZ
for c = 1 to 52
' Print value of cell in immediate window
debug.print cells(r,c)
next c
next r
end sub

Cheers,
Jason Lepack

On Jan 30, 7:08 am, mcphc wrote:
I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)

Column letter reference as number reference
 

Sub Tester1()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, cell As Range
Set rng = ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea)
Set rng1 = rng.Rows(1).Cells
Set rng2 = rng.Columns(1).Cells
For j = rng1(1).Column To rng1(rng1.Count).Column - 5
For i = rng2(1).Row To rng2(rng2.Count).Row
Set cell = Cells(i, j)
cell.Interior.ColorIndex = 3
Next i, j

End Sub

--
Regards,
Tom Ogilvy



"mcphc" wrote:

Thanks for the help guys but not quiet what I was looking for.

The problem is the print area is always different so it could be

For col= 1 to 52

or

For col = 1 to 30

I need something like

For col= 1 to PrintAreaEdge

where PrintAreaEdge is the last vertical column number (ie 52 not letter
"AZ") in the print area.

Using the below

For Each a In Range("print_Area")
MsgBox a
Next

is close but I actually only want to search a few columns within the print
area
ie

For col = 1 to PrintAreaEdge - 5

Thanks

"Jason Lepack" wrote:

public sub mcphc()
dim r as integer, c as integer
' rows 1 to 57
for r = 1 to 57
' columns A to AZ
for c = 1 to 52
' Print value of cell in immediate window
debug.print cells(r,c)
next c
next r
end sub

Cheers,
Jason Lepack

On Jan 30, 7:08 am, mcphc wrote:
I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)

Column letter reference as number reference
 

That got it! Cheers Tom

"Tom Ogilvy" wrote:

Sub Tester1()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, cell As Range
Set rng = ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea)
Set rng1 = rng.Rows(1).Cells
Set rng2 = rng.Columns(1).Cells
For j = rng1(1).Column To rng1(rng1.Count).Column - 5
For i = rng2(1).Row To rng2(rng2.Count).Row
Set cell = Cells(i, j)
cell.Interior.ColorIndex = 3
Next i, j

End Sub

--
Regards,
Tom Ogilvy



"mcphc" wrote:

Thanks for the help guys but not quiet what I was looking for.

The problem is the print area is always different so it could be

For col= 1 to 52

or

For col = 1 to 30

I need something like

For col= 1 to PrintAreaEdge

where PrintAreaEdge is the last vertical column number (ie 52 not letter
"AZ") in the print area.

Using the below

For Each a In Range("print_Area")
MsgBox a
Next

is close but I actually only want to search a few columns within the print
area
ie

For col = 1 to PrintAreaEdge - 5

Thanks

"Jason Lepack" wrote:

public sub mcphc()
dim r as integer, c as integer
' rows 1 to 57
for r = 1 to 57
' columns A to AZ
for c = 1 to 52
' Print value of cell in immediate window
debug.print cells(r,c)
next c
next r
end sub

Cheers,
Jason Lepack

On Jan 30, 7:08 am, mcphc wrote:
I use the below to get the vertical edge of the print area.

Range("Print_Area").Address

The output of this is something like "$A$:1$AZ$57".

I would like to run a macro that checks each cell in a row from column "A"
to (as in the above) column "AZ"

I would like to use something like

For col = 1 to 52

How do you represent AZ as 52? Or is there a better way of doing this? (I'm
sure there is)