Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create index and perfrom function on each and every value.
Hi,
See worksheet below: | A | B | --+----------+------------| 1 | 20051101 | 28/11/2005 | --+----------+------------| 2 | 20051108 | | --+----------+------------| 3 | 20051125 | | --+----------+------------| 4 | 20051128 | | --------------------------| I want to convert every cell in column A to dd/mm/yyyy date format. I have done it for coumn B, but that is made easy by there only being on cell that needs changing: Application.Goto Reference:=Worksheets("IntefaceSheet").Range("B1") , _ Scroll:=True IDATE1 = ActiveCell IDATE2 = Mid(IDATE1, 1, 4) IDATE3 = Mid(IDATE1, 5, 2) IDATE4 = Mid(IDATE1, 7, 2) ActiveCell.FormulaR1C1 = IDATE2 + "/" + IDATE3 + "/" + IDATE4 Now I need to do the same thing for every cell that has a value in column A. This has to be done in Excel and not in Access. I have a long Excel macro that is going to create an interface file (interface.xls) for importing a dodgy CSV file into an Access 2003 DB, so therefore the formatting and data-type conversion all has to be done in Excel before the data is imported into Access 2003. The code in the Access DB launches the Excel file+Macro, the data is then imported into Access and the blank values in column B are updated in Access so that it looks like this: | pick_dt | inv_dt | --+----------+------------| | 20051101 | 28/11/2005 | --+----------+------------| | 20051108 | 28/11/2005 | --+----------+------------| | 20051125 | 28/11/2005 | --+----------+------------| | 20051128 | 28/11/2005 | --------------------------| So now I just need to convert the values in column A to a date, but in Access the field WILL ultimately be of data-type date/time, so it won't import a number. That is why it has to be converted in Excell first and then imported into Access. Any ideas? I figure that I should load the range (A:A) as an index and then do the above function on each value in the index. But I am struggling to work out what the code is to create an index that loads a range of Excel cells and then loop through each and then performs the above funtion on every cell that has a value (Is Not Null). I am sure it is easy, but I seem to have developed a mental block. Maybe someon looking at it afresh will see their way round it more easily. TIA, Jarryd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create index and perfrom function on each and every value.
Here is some code to do that bit
Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow Cells(i, "A").Value = DateSerial(Cells(i, "A").Value \ 10000, _ (Cells(i, "A").Value Mod 10000) \ 100, Cells(i, "A").Value Mod 100) Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Jarryd" wrote in message ... Hi, See worksheet below: | A | B | --+----------+------------| 1 | 20051101 | 28/11/2005 | --+----------+------------| 2 | 20051108 | | --+----------+------------| 3 | 20051125 | | --+----------+------------| 4 | 20051128 | | --------------------------| I want to convert every cell in column A to dd/mm/yyyy date format. I have done it for coumn B, but that is made easy by there only being on cell that needs changing: Application.Goto Reference:=Worksheets("IntefaceSheet").Range("B1") , _ Scroll:=True IDATE1 = ActiveCell IDATE2 = Mid(IDATE1, 1, 4) IDATE3 = Mid(IDATE1, 5, 2) IDATE4 = Mid(IDATE1, 7, 2) ActiveCell.FormulaR1C1 = IDATE2 + "/" + IDATE3 + "/" + IDATE4 Now I need to do the same thing for every cell that has a value in column A. This has to be done in Excel and not in Access. I have a long Excel macro that is going to create an interface file (interface.xls) for importing a dodgy CSV file into an Access 2003 DB, so therefore the formatting and data-type conversion all has to be done in Excel before the data is imported into Access 2003. The code in the Access DB launches the Excel file+Macro, the data is then imported into Access and the blank values in column B are updated in Access so that it looks like this: | pick_dt | inv_dt | --+----------+------------| | 20051101 | 28/11/2005 | --+----------+------------| | 20051108 | 28/11/2005 | --+----------+------------| | 20051125 | 28/11/2005 | --+----------+------------| | 20051128 | 28/11/2005 | --------------------------| So now I just need to convert the values in column A to a date, but in Access the field WILL ultimately be of data-type date/time, so it won't import a number. That is why it has to be converted in Excell first and then imported into Access. Any ideas? I figure that I should load the range (A:A) as an index and then do the above function on each value in the index. But I am struggling to work out what the code is to create an index that loads a range of Excel cells and then loop through each and then performs the above funtion on every cell that has a value (Is Not Null). I am sure it is easy, but I seem to have developed a mental block. Maybe someon looking at it afresh will see their way round it more easily. TIA, Jarryd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create index and perfrom function on each and every value.
Excellent!! Thanks a mill, Bob!
Jarryd "Bob Phillips" wrote in message ... Here is some code to do that bit Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow Cells(i, "A").Value = DateSerial(Cells(i, "A").Value \ 10000, _ (Cells(i, "A").Value Mod 10000) \ 100, Cells(i, "A").Value Mod 100) Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Jarryd" wrote in message ... Hi, See worksheet below: | A | B | --+----------+------------| 1 | 20051101 | 28/11/2005 | --+----------+------------| 2 | 20051108 | | --+----------+------------| 3 | 20051125 | | --+----------+------------| 4 | 20051128 | | --------------------------| I want to convert every cell in column A to dd/mm/yyyy date format. I have done it for coumn B, but that is made easy by there only being on cell that needs changing: Application.Goto Reference:=Worksheets("IntefaceSheet").Range("B1") , _ Scroll:=True IDATE1 = ActiveCell IDATE2 = Mid(IDATE1, 1, 4) IDATE3 = Mid(IDATE1, 5, 2) IDATE4 = Mid(IDATE1, 7, 2) ActiveCell.FormulaR1C1 = IDATE2 + "/" + IDATE3 + "/" + IDATE4 Now I need to do the same thing for every cell that has a value in column A. This has to be done in Excel and not in Access. I have a long Excel macro that is going to create an interface file (interface.xls) for importing a dodgy CSV file into an Access 2003 DB, so therefore the formatting and data-type conversion all has to be done in Excel before the data is imported into Access 2003. The code in the Access DB launches the Excel file+Macro, the data is then imported into Access and the blank values in column B are updated in Access so that it looks like this: | pick_dt | inv_dt | --+----------+------------| | 20051101 | 28/11/2005 | --+----------+------------| | 20051108 | 28/11/2005 | --+----------+------------| | 20051125 | 28/11/2005 | --+----------+------------| | 20051128 | 28/11/2005 | --------------------------| So now I just need to convert the values in column A to a date, but in Access the field WILL ultimately be of data-type date/time, so it won't import a number. That is why it has to be converted in Excell first and then imported into Access. Any ideas? I figure that I should load the range (A:A) as an index and then do the above function on each value in the index. But I am struggling to work out what the code is to create an index that loads a range of Excel cells and then loop through each and then performs the above funtion on every cell that has a value (Is Not Null). I am sure it is easy, but I seem to have developed a mental block. Maybe someon looking at it afresh will see their way round it more easily. TIA, Jarryd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i create an index for my book? | Excel Discussion (Misc queries) | |||
Create Dependent Lists using an INDEX function?? | New Users to Excel | |||
hyperlinks to create an index | Excel Discussion (Misc queries) | |||
Create an Index Sheet | Excel Programming | |||
Create worksheet index | Excel Programming |