| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating formatting without taking each cell to edit mode
Hello all!
I have some worksheets that I get from a database and upon exporting them some items that should be text (Stock numbers with leading zeros) are saved as Numbers and the leading zeros are removed. Wel... I can easily get the leading zeros back by formatting to custom"000000000". The problem I am having however is getting the cells in that column to Update to the new format. I find that I must dbl-Click in each cell and enter "edit mode" for each cell to get it to update. Is there any one out there taht knows of the code t o automate the update as I have thousands of cells to update in this column each week. -- Thanks, Patrick |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating formatting without taking each cell to edit mode
what is the length to the field in the database you are exporting from ?
"PSKelligan" wrote: Hello all! I have some worksheets that I get from a database and upon exporting them some items that should be text (Stock numbers with leading zeros) are saved as Numbers and the leading zeros are removed. Wel... I can easily get the leading zeros back by formatting to custom"000000000". The problem I am having however is getting the cells in that column to Update to the new format. I find that I must dbl-Click in each cell and enter "edit mode" for each cell to get it to update. Is there any one out there taht knows of the code t o automate the update as I have thousands of cells to update in this column each week. -- Thanks, Patrick |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating formatting without taking each cell to edit mode
Before exporting from the database create a query that changes the field by
adding a prefix to it. Say the field name es StockNo then in the query create a field like: StNo:="S"&[Stockno]. This will assue you that the data will be exported as text "PSKelligan" wrote: Hello all! I have some worksheets that I get from a database and upon exporting them some items that should be text (Stock numbers with leading zeros) are saved as Numbers and the leading zeros are removed. Wel... I can easily get the leading zeros back by formatting to custom"000000000". The problem I am having however is getting the cells in that column to Update to the new format. I find that I must dbl-Click in each cell and enter "edit mode" for each cell to get it to update. Is there any one out there taht knows of the code t o automate the update as I have thousands of cells to update in this column each week. -- Thanks, Patrick |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating formatting without taking each cell to edit mode
I must make the changes after export. I am unable to access the database via
dynamic query. There are pre-defined views that I can use and no flexibility as this dbate is no my product. I export through a web interface and I want to clean up the data in excel befor importing it into an Access database. the length of the field is 9 characters. All numeric and all with 1 or 2 leading zeros. Any idea on how to update the formatting of the cells? thanks, Patrick |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating formatting without taking each cell to edit mode
I am assuming the stock number is in column A and the data begins on line 2
In a new column use the formula =text(if(len(A1<8,"00"&A1,if(len(A1<9,"0"&A1,A1))) "PSKelligan" wrote: I must make the changes after export. I am unable to access the database via dynamic query. There are pre-defined views that I can use and no flexibility as this dbate is no my product. I export through a web interface and I want to clean up the data in excel befor importing it into an Access database. the length of the field is 9 characters. All numeric and all with 1 or 2 leading zeros. Any idea on how to update the formatting of the cells? thanks, Patrick |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating formatting without taking each cell to edit mode
I typed the formula wrong. It should be as follows
=(IF(LEN(A1)<8,"00"&A1,IF(LEN(A1)<9,"0"&A1,A1)) "PSKelligan" wrote: I must make the changes after export. I am unable to access the database via dynamic query. There are pre-defined views that I can use and no flexibility as this dbate is no my product. I export through a web interface and I want to clean up the data in excel befor importing it into an Access database. the length of the field is 9 characters. All numeric and all with 1 or 2 leading zeros. Any idea on how to update the formatting of the cells? thanks, Patrick |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating formatting without taking each cell to edit mode
Thanks for the response,
This seems to work but is not very efficient when working with several exported sheets and upwards of 10000 records. I am actually looking for a "VBA" proceedure that will, with a tool bar button click, update formatting to the entire used range on a sheet. Thanks, Patrick "LWendel" wrote: I typed the formula wrong. It should be as follows =(IF(LEN(A1)<8,"00"&A1,IF(LEN(A1)<9,"0"&A1,A1)) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating formatting without taking each cell to edit mode
I came up with a solution that seems to do the trick. Code follows.
Code:
Sub amss_NIIN_Converter()
Dim myVal As String
Dim myCnt As Integer
Dim x As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Columns("I:I").Select
Selection.NumberFormat = "00-000-0000"
Cells(2, 9).Select
myCnt = Range("I2", Cells(2, 9).End(xlDown)).Count + 1
For x = 2 To myCnt
Cells(x, 9).Select
myVal = ActiveCell.Value
Selection.FormulaR1C1 = myVal
Next x
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible to arrow to a different cell while in edit mode? | Excel Discussion (Misc queries) | |||
| Taking the Mode based on cell value | Excel Discussion (Misc queries) | |||
| Taking the mode based on Cell value | Excel Discussion (Misc queries) | |||
| cell value in edit mode with COM addin | Excel Programming | |||
| Detecting Cell Edit Mode | Excel Programming | |||
Linear Mode
