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)

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)

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" <(E-Mail Removed)> wrote in message news:3127A9DD-E2F5-4B80-9ADC-(E-Mail Removed)...
|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)

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 <m...@discussions.microsoft.com> 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)

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 <m...@discussions.microsoft.com> 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)
>
>

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

On Jan 30, 8:23 am, mcphc <m...@discussions.microsoft.com> 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 <m...@discussions.microsoft.com> 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 -

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 <m...@discussions.microsoft.com> 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)
> >
> >

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 <m...@discussions.microsoft.com> 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)
> >
> >

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 <m...@discussions.microsoft.com> 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)
> > >
> > >