Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through columns
Excel 2003
Hello. I have this code from a macro and am looking to streamline it: <begin macro Columns("A:A").Select Selection.NumberFormat = "General" Columns("B:B").Select Selection.NumberFormat = "General" Columns("C:C").Select Selection.NumberFormat = "General" Columns("D:D").Select Selection.NumberFormat = "@" Columns("E:E").Select Selection.NumberFormat = "@" Columns("F:F").Select Selection.NumberFormat = "General" Columns("G:G").Select Selection.NumberFormat = "@" Columns("H:H").Select Columns("I:I").Select Selection.NumberFormat = "@" Columns("J:J").Select Selection.NumberFormat = "0" Columns("K:K").Select Selection.NumberFormat = "0" <end macro I am not sure how to set up code to loop through an .XLS file to get the sheetname, count the valid columns and rows (column/row numbers can change from month to month) Then as I loop through each column if it is A,B,C, or F then set numberformat to "General", etc..... Do I have to invoke the Excel Object -and- the Workbook object, -and- the sheet object? This is where I get confused. Thnx.... *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through columns
I'm not sure I understand what you're doing, but if you wanted to loop through
all the worksheets in the activeworkbook, you could do something like: Dim Wks as worksheet for each wks in activeworkbook.worksheets with wks .range("a1:c1,f1").entirecolumn.numberformat = "General" .Range("d1:E1,g1,i1,j1,k1").entirecolumn.numberfor mat = "@" .range("j1:k1").entirecolumn.numberformat = "0" end with next wks I was confused about what happens with column H, though. RLN wrote: Excel 2003 Hello. I have this code from a macro and am looking to streamline it: <begin macro Columns("A:A").Select Selection.NumberFormat = "General" Columns("B:B").Select Selection.NumberFormat = "General" Columns("C:C").Select Selection.NumberFormat = "General" Columns("D:D").Select Selection.NumberFormat = "@" Columns("E:E").Select Selection.NumberFormat = "@" Columns("F:F").Select Selection.NumberFormat = "General" Columns("G:G").Select Selection.NumberFormat = "@" Columns("H:H").Select Columns("I:I").Select Selection.NumberFormat = "@" Columns("J:J").Select Selection.NumberFormat = "0" Columns("K:K").Select Selection.NumberFormat = "0" <end macro I am not sure how to set up code to loop through an .XLS file to get the sheetname, count the valid columns and rows (column/row numbers can change from month to month) Then as I loop through each column if it is A,B,C, or F then set numberformat to "General", etc..... Do I have to invoke the Excel Object -and- the Workbook object, -and- the sheet object? This is where I get confused. Thnx.... *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through columns
Your solution worked great, Dave, thank you! I appreciate your help very much. RLN *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping through columns | Excel Programming | |||
looping across columns in range? | Excel Discussion (Misc queries) | |||
Looping thru columns | Excel Programming | |||
Looping through columns | Excel Programming | |||
Looping thru columns beyond Z | Excel Programming |