Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference a number from a different column in a formula | Excel Worksheet Functions | |||
Extract Column Letter from Cell Reference in another Cell | Excel Discussion (Misc queries) | |||
Reference a Column by Number | Excel Programming | |||
Selecting a range using a column number reference | Excel Programming | |||
reference to column (not known) and certain row number | Excel Programming |