| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have a fix already, but would like to use what I've created here
I know why I'm getting the error, but I would like to know how to fix it.
The spreadsheet cells contain the following: A1: StartDate A2: 12/12/08 B1: EndDate B2: 12/15/08 F1: StartTime (Unplanned) F2: 12:20 G1: EndTime (Unplanned) G2: 14:30 I1: TotalDowntime I2: This cell is blank and where the end result of Main() will go. ________________________________ Sub Main() Range("A1").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _ MatchCase:=False).Activate ActiveCell.Offset(-1, 8).Activate startdate = ActiveCell.Offset(0, -8).Value enddate = ActiveCell.Offset(0, -7).Value daysdown = DateDiff("D", startdate, enddate) starttime = ActiveCell.Offset(0, -3).Value endtime = ActiveCell.Offset(0, -2).Value hourdiff = starttime - endtime daysvshours = daysdown * 24 & ":00:00" totalhours = daysvshours - hourdiff <-------------Gives the error "Type Mismatch (Error 13)" ActiveCell.Value = totalhours End Sub __________________ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have a fix already, but would like to use what I've created here
Comment out the formatting of the daysvshours variable live this only:
daysvshours = daysdown * 24 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Optatus" wrote: I know why I'm getting the error, but I would like to know how to fix it. The spreadsheet cells contain the following: A1: StartDate A2: 12/12/08 B1: EndDate B2: 12/15/08 F1: StartTime (Unplanned) F2: 12:20 G1: EndTime (Unplanned) G2: 14:30 I1: TotalDowntime I2: This cell is blank and where the end result of Main() will go. ________________________________ Sub Main() Range("A1").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _ MatchCase:=False).Activate ActiveCell.Offset(-1, 8).Activate startdate = ActiveCell.Offset(0, -8).Value enddate = ActiveCell.Offset(0, -7).Value daysdown = DateDiff("D", startdate, enddate) starttime = ActiveCell.Offset(0, -3).Value endtime = ActiveCell.Offset(0, -2).Value hourdiff = starttime - endtime daysvshours = daysdown * 24 & ":00:00" totalhours = daysvshours - hourdiff <-------------Gives the error "Type Mismatch (Error 13)" ActiveCell.Value = totalhours End Sub __________________ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have a fix already, but would like to use what I've created
Well when I do that I don't exactly get what I'm looking for the outcome.
Here's what I get 1730:10:00 Here's what it should be: These are returned if they are set to the ActiveCell.Value at the end of the mod... daysvsHours should return: 72:00:00 hourdiff should return: -0.0902777777777777 This should be the endresult of the original mod without any modifications.... totalhours should be 74:10:00 I know that it doesn't like the string in daysvshours... is there a way to fix that without populating two cells and subtracting two different cells (which is the work around that works). "Michael" wrote: Comment out the formatting of the daysvshours variable live this only: daysvshours = daysdown * 24 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Optatus" wrote: I know why I'm getting the error, but I would like to know how to fix it. The spreadsheet cells contain the following: A1: StartDate A2: 12/12/08 B1: EndDate B2: 12/15/08 F1: StartTime (Unplanned) F2: 12:20 G1: EndTime (Unplanned) G2: 14:30 I1: TotalDowntime I2: This cell is blank and where the end result of Main() will go. ________________________________ Sub Main() Range("A1").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _ MatchCase:=False).Activate ActiveCell.Offset(-1, 8).Activate startdate = ActiveCell.Offset(0, -8).Value enddate = ActiveCell.Offset(0, -7).Value daysdown = DateDiff("D", startdate, enddate) starttime = ActiveCell.Offset(0, -3).Value endtime = ActiveCell.Offset(0, -2).Value hourdiff = starttime - endtime daysvshours = daysdown * 24 & ":00:00" totalhours = daysvshours - hourdiff <-------------Gives the error "Type Mismatch (Error 13)" ActiveCell.Value = totalhours End Sub __________________ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have a fix already, but would like to use what I've created
I get 71.48611111 on I2
-- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Optatus" wrote: Well when I do that I don't exactly get what I'm looking for the outcome. Here's what I get 1730:10:00 Here's what it should be: These are returned if they are set to the ActiveCell.Value at the end of the mod... daysvsHours should return: 72:00:00 hourdiff should return: -0.0902777777777777 This should be the endresult of the original mod without any modifications.... totalhours should be 74:10:00 I know that it doesn't like the string in daysvshours... is there a way to fix that without populating two cells and subtracting two different cells (which is the work around that works). "Michael" wrote: Comment out the formatting of the daysvshours variable live this only: daysvshours = daysdown * 24 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Optatus" wrote: I know why I'm getting the error, but I would like to know how to fix it. The spreadsheet cells contain the following: A1: StartDate A2: 12/12/08 B1: EndDate B2: 12/15/08 F1: StartTime (Unplanned) F2: 12:20 G1: EndTime (Unplanned) G2: 14:30 I1: TotalDowntime I2: This cell is blank and where the end result of Main() will go. ________________________________ Sub Main() Range("A1").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _ MatchCase:=False).Activate ActiveCell.Offset(-1, 8).Activate startdate = ActiveCell.Offset(0, -8).Value enddate = ActiveCell.Offset(0, -7).Value daysdown = DateDiff("D", startdate, enddate) starttime = ActiveCell.Offset(0, -3).Value endtime = ActiveCell.Offset(0, -2).Value hourdiff = starttime - endtime daysvshours = daysdown * 24 & ":00:00" totalhours = daysvshours - hourdiff <-------------Gives the error "Type Mismatch (Error 13)" ActiveCell.Value = totalhours End Sub __________________ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have a fix already, but would like to use what I've created
My apologie, I get 72.09027778 i did not have the end time
If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Optatus" wrote: Well when I do that I don't exactly get what I'm looking for the outcome. Here's what I get 1730:10:00 Here's what it should be: These are returned if they are set to the ActiveCell.Value at the end of the mod... daysvsHours should return: 72:00:00 hourdiff should return: -0.0902777777777777 This should be the endresult of the original mod without any modifications.... totalhours should be 74:10:00 I know that it doesn't like the string in daysvshours... is there a way to fix that without populating two cells and subtracting two different cells (which is the work around that works). "Michael" wrote: Comment out the formatting of the daysvshours variable live this only: daysvshours = daysdown * 24 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Optatus" wrote: I know why I'm getting the error, but I would like to know how to fix it. The spreadsheet cells contain the following: A1: StartDate A2: 12/12/08 B1: EndDate B2: 12/15/08 F1: StartTime (Unplanned) F2: 12:20 G1: EndTime (Unplanned) G2: 14:30 I1: TotalDowntime I2: This cell is blank and where the end result of Main() will go. ________________________________ Sub Main() Range("A1").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _ MatchCase:=False).Activate ActiveCell.Offset(-1, 8).Activate startdate = ActiveCell.Offset(0, -8).Value enddate = ActiveCell.Offset(0, -7).Value daysdown = DateDiff("D", startdate, enddate) starttime = ActiveCell.Offset(0, -3).Value endtime = ActiveCell.Offset(0, -2).Value hourdiff = starttime - endtime daysvshours = daysdown * 24 & ":00:00" totalhours = daysvshours - hourdiff <-------------Gives the error "Type Mismatch (Error 13)" ActiveCell.Value = totalhours End Sub __________________ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have a fix already, but would like to use what I've created
If you get 1730:10:00 is because your Cell I2 is formatted as time -
37:30:55; which means you should reformat your cell to General -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Michael" wrote: My apologie, I get 72.09027778 i did not have the end time If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Optatus" wrote: Well when I do that I don't exactly get what I'm looking for the outcome. Here's what I get 1730:10:00 Here's what it should be: These are returned if they are set to the ActiveCell.Value at the end of the mod... daysvsHours should return: 72:00:00 hourdiff should return: -0.0902777777777777 This should be the endresult of the original mod without any modifications.... totalhours should be 74:10:00 I know that it doesn't like the string in daysvshours... is there a way to fix that without populating two cells and subtracting two different cells (which is the work around that works). "Michael" wrote: Comment out the formatting of the daysvshours variable live this only: daysvshours = daysdown * 24 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Optatus" wrote: I know why I'm getting the error, but I would like to know how to fix it. The spreadsheet cells contain the following: A1: StartDate A2: 12/12/08 B1: EndDate B2: 12/15/08 F1: StartTime (Unplanned) F2: 12:20 G1: EndTime (Unplanned) G2: 14:30 I1: TotalDowntime I2: This cell is blank and where the end result of Main() will go. ________________________________ Sub Main() Range("A1").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _ MatchCase:=False).Activate ActiveCell.Offset(-1, 8).Activate startdate = ActiveCell.Offset(0, -8).Value enddate = ActiveCell.Offset(0, -7).Value daysdown = DateDiff("D", startdate, enddate) starttime = ActiveCell.Offset(0, -3).Value endtime = ActiveCell.Offset(0, -2).Value hourdiff = starttime - endtime daysvshours = daysdown * 24 & ":00:00" totalhours = daysvshours - hourdiff <-------------Gives the error "Type Mismatch (Error 13)" ActiveCell.Value = totalhours End Sub __________________ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can such a macro be created? | Excel Discussion (Misc queries) | |||
| tab key use in a created template | Excel Discussion (Misc queries) | |||
| Created Date | Excel Worksheet Functions | |||
| Running VBA created in XL for Win on Mac | Excel Discussion (Misc queries) | |||
| created objects look different | Excel Programming | |||
Hybrid Mode
