| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Cell
Hello,
I am new to Excel programming so I would appreciate some guidance. I would like to loop through the second row of each column to find the cell format and then format to a specific way. For instance, if the cell is general or text then left alight, if it is a number then right align and format to "0,000", if date then centre align and format dd/mm/yy. Can someone point me in the right direction here. I have tried piece together code from this site but cannot make much sense of it. Many thanks, Martin |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format Cell
Martin,
Right click your sheet tab, view code and paste this in and run it Sub missive() lastcol = Range("IV2").End(xlToLeft).Address Set myrange = Range("$A$2:" & lastcol) For Each c In myrange If WorksheetFunction.IsText(c) Then c.HorizontalAlignment = xlLeft ElseIf IsDate(c) Then c.HorizontalAlignment = xlCenter c.NumberFormat = "dd/mm/yyyy" ElseIf IsNumeric(c) Then c.NumberFormat = "0,000" c.HorizontalAlignment = xlRight End If Next End Sub Mike "Martin" wrote: Hello, I am new to Excel programming so I would appreciate some guidance. I would like to loop through the second row of each column to find the cell format and then format to a specific way. For instance, if the cell is general or text then left alight, if it is a number then right align and format to "0,000", if date then centre align and format dd/mm/yy. Can someone point me in the right direction here. I have tried piece together code from this site but cannot make much sense of it. Many thanks, Martin |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format Cell
Thanks Mike, worked like a treat. It also helps me to understand how it
works now. Many thanks. Martin "Mike H" wrote: Martin, Right click your sheet tab, view code and paste this in and run it Sub missive() lastcol = Range("IV2").End(xlToLeft).Address Set myrange = Range("$A$2:" & lastcol) For Each c In myrange If WorksheetFunction.IsText(c) Then c.HorizontalAlignment = xlLeft ElseIf IsDate(c) Then c.HorizontalAlignment = xlCenter c.NumberFormat = "dd/mm/yyyy" ElseIf IsNumeric(c) Then c.NumberFormat = "0,000" c.HorizontalAlignment = xlRight End If Next End Sub Mike "Martin" wrote: Hello, I am new to Excel programming so I would appreciate some guidance. I would like to loop through the second row of each column to find the cell format and then format to a specific way. For instance, if the cell is general or text then left alight, if it is a number then right align and format to "0,000", if date then centre align and format dd/mm/yy. Can someone point me in the right direction here. I have tried piece together code from this site but cannot make much sense of it. Many thanks, Martin |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format Cell
Martin, instead of using "0,000", you should consider "#,##0". This will
always give you a number (the zero), but not leading zeroes. -- Brad E. "Martin" wrote: Thanks Mike, worked like a treat. It also helps me to understand how it works now. Many thanks. Martin "Mike H" wrote: Martin, Right click your sheet tab, view code and paste this in and run it Sub missive() lastcol = Range("IV2").End(xlToLeft).Address Set myrange = Range("$A$2:" & lastcol) For Each c In myrange If WorksheetFunction.IsText(c) Then c.HorizontalAlignment = xlLeft ElseIf IsDate(c) Then c.HorizontalAlignment = xlCenter c.NumberFormat = "dd/mm/yyyy" ElseIf IsNumeric(c) Then c.NumberFormat = "0,000" c.HorizontalAlignment = xlRight End If Next End Sub Mike "Martin" wrote: Hello, I am new to Excel programming so I would appreciate some guidance. I would like to loop through the second row of each column to find the cell format and then format to a specific way. For instance, if the cell is general or text then left alight, if it is a number then right align and format to "0,000", if date then centre align and format dd/mm/yy. Can someone point me in the right direction here. I have tried piece together code from this site but cannot make much sense of it. Many thanks, Martin |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format Cell
Thank you. I reaslised my mistake when I ran it for the first time.
Martin "Brad E." wrote: Martin, instead of using "0,000", you should consider "#,##0". This will always give you a number (the zero), but not leading zeroes. -- Brad E. "Martin" wrote: Thanks Mike, worked like a treat. It also helps me to understand how it works now. Many thanks. Martin "Mike H" wrote: Martin, Right click your sheet tab, view code and paste this in and run it Sub missive() lastcol = Range("IV2").End(xlToLeft).Address Set myrange = Range("$A$2:" & lastcol) For Each c In myrange If WorksheetFunction.IsText(c) Then c.HorizontalAlignment = xlLeft ElseIf IsDate(c) Then c.HorizontalAlignment = xlCenter c.NumberFormat = "dd/mm/yyyy" ElseIf IsNumeric(c) Then c.NumberFormat = "0,000" c.HorizontalAlignment = xlRight End If Next End Sub Mike "Martin" wrote: Hello, I am new to Excel programming so I would appreciate some guidance. I would like to loop through the second row of each column to find the cell format and then format to a specific way. For instance, if the cell is general or text then left alight, if it is a number then right align and format to "0,000", if date then centre align and format dd/mm/yy. Can someone point me in the right direction here. I have tried piece together code from this site but cannot make much sense of it. Many thanks, Martin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
| cell format - remove cell format pattern without effecting colors | Excel Discussion (Misc queries) | |||
| Can cell format come from and change with reference cell format | Excel Discussion (Misc queries) | |||
| how do i format a cell based on format of a range of cells? | Excel Discussion (Misc queries) | |||
| How do I copy data in single cell format to a merged cell format | Excel Discussion (Misc queries) | |||
Linear Mode
