Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting values populated by VB Code
I am getting data to show properly in the worksheets being populated by the
VB code, however, I need to convert it and do not know what to do in the VB code to do this. For example, I am copying times in seconds '2125' but need it to read in HH:MM:SS format. I would usually do it in this method: = value/86400 OR 2125/86400 and then format the cell as 'HH:MM:SS' to show the value of: 0:35:25. Is there something I can do after copying the cell and before its applied to the cell its supposed to go into? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting values populated by VB Code
You need to convert seconds to days by dividing by 86400. The format the
cell using this VBA code Range("A1").numberformat = "HH:MM:SS" "Dakota" wrote: I am getting data to show properly in the worksheets being populated by the VB code, however, I need to convert it and do not know what to do in the VB code to do this. For example, I am copying times in seconds '2125' but need it to read in HH:MM:SS format. I would usually do it in this method: = value/86400 OR 2125/86400 and then format the cell as 'HH:MM:SS' to show the value of: 0:35:25. Is there something I can do after copying the cell and before its applied to the cell its supposed to go into? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting values populated by VB Code
Joel,
I know I need to convert it but how to I divide the cell value the VBA code is getting and format it before its copied to a new sheet? Here is the code I have now: If sh.Cells(2, "B").Value = cell Then fDate = cell.Offset(0, 1).Value cell.Offset(0, 8).Copy Set c = sh.Range("A15:A45").Find(fDate, LookIn:=xlValues) Range("A1").numberformat = "HH:MM:SS" sh.Range("C" & c.Row).PasteSpecial xlPasteValues This copies it as 'general' and shows '2125' in the cell. I need to to show 0:35:25 "Joel" wrote: You need to convert seconds to days by dividing by 86400. The format the cell using this VBA code Range("A1").numberformat = "HH:MM:SS" "Dakota" wrote: I am getting data to show properly in the worksheets being populated by the VB code, however, I need to convert it and do not know what to do in the VB code to do this. For example, I am copying times in seconds '2125' but need it to read in HH:MM:SS format. I would usually do it in this method: = value/86400 OR 2125/86400 and then format the cell as 'HH:MM:SS' to show the value of: 0:35:25. Is there something I can do after copying the cell and before its applied to the cell its supposed to go into? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting values populated by VB Code
If sh.Cells(2, "B").Value = cell Then
fDate = cell.Offset(0, 1).Value cell.Offset(0, 8).Copy Set c = sh.Range("A15:A45").Find(fDate, LookIn:=xlValues) Range("A1").numberformat = "HH:MM:SS" sh.Range("C" & c.Row).PasteSpecial xlPasteValues sh.Range("C" & c.Row) = sh.Range("C" & c.Row)/86400 You don't need to copy instead fDate = cell.Offset(0, 1).Value Set c = sh.Range("A15:A45").Find(fDate, LookIn:=xlValues) Range("A1").numberformat = "HH:MM:SS" sh.Range("C" & c.Row) = cell.Offset(0, 8).value/86400 "Dakota" wrote: Joel, I know I need to convert it but how to I divide the cell value the VBA code is getting and format it before its copied to a new sheet? Here is the code I have now: If sh.Cells(2, "B").Value = cell Then fDate = cell.Offset(0, 1).Value cell.Offset(0, 8).Copy Set c = sh.Range("A15:A45").Find(fDate, LookIn:=xlValues) Range("A1").numberformat = "HH:MM:SS" sh.Range("C" & c.Row).PasteSpecial xlPasteValues This copies it as 'general' and shows '2125' in the cell. I need to to show 0:35:25 "Joel" wrote: You need to convert seconds to days by dividing by 86400. The format the cell using this VBA code Range("A1").numberformat = "HH:MM:SS" "Dakota" wrote: I am getting data to show properly in the worksheets being populated by the VB code, however, I need to convert it and do not know what to do in the VB code to do this. For example, I am copying times in seconds '2125' but need it to read in HH:MM:SS format. I would usually do it in this method: = value/86400 OR 2125/86400 and then format the cell as 'HH:MM:SS' to show the value of: 0:35:25. Is there something I can do after copying the cell and before its applied to the cell its supposed to go into? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting dependent on whether cell is populated. | Excel Discussion (Misc queries) | |||
Function Not Recognising Values in Populated Ranges | Excel Programming | |||
Conditional formulas: color code text populated cells? | Excel Worksheet Functions | |||
HELP! - Printing a sheet with values populated from dropdown box | Excel Programming | |||
Copy Values to Below last Populated Cell Q | Excel Programming |