I have a userform for a time sheet i am working on. I used the following code to enter the time difference between time in and out.
Code:
'FIRST SET
v = (Me.TimeIn.Value)
w = (Me.TimeOut.Value)
x = Int(v / 100) + (((v / 100) - Int(v / 100)) / 0.6)
y = Int(w / 100) + (((w / 100) - Int(w / 100)) / 0.6)
[Code] ........
The code works fine, however, I have I need to place another time difference in based on from the sameuserform but different comboboxes. I thought it wouldbe easy just to add a second code.
Code:
v = (Me.TimeIn.Value)
w = (Me.TimeOut.Value)
x = Int(v / 100) + (((v / 100) - Int(v / 100)) / 0.6)
y = Int(w / 100) + (((w / 100) - Int(w / 100)) / 0.6)
[Code] ....
When i add the second code i get the runtime error 13. The worse part is that i was going to do this like five times.
I have a user form that builds a workbook from one template sheet and information in a SQL Server database. The template sheet contains 4 charts that are copied to every new sheet in the workbook, then their data ranges are set through VBA code. For a while the code was working wonderfully. All the necessary sheets were being generated and correctly populated.
Yesterday I added a new module to generate another sheet and pull data from the same SQL Server tables. Now if I run the userform it will work fine once and create all the needed sheets, and set all the charts to the proper ranges. However, if I delete all the created sheets and run the form again, I get:
Runtime Error '-2147417848 (80010108)'
Automation Error The Object Invoked has disconnected from its clients
The error consistantly occurs for the following line of Selection.Copy Destination:=Worksheets("Master").Range("D1")
After the error the selected range still shows up as surrounded by a selection box, but if I change sheets, the box moves to the new sheet. Also I cannot select any new cells in the work book. In order to get Excel to work again I have to End Task from the Task Manager just to get it to close. When I restart Excel and go back to my work book I get the same results, i.e. The form works great once, then Excel dies.
I have tried qualifing all of the objects that can be qualified. I have tried setting all possible varriables to Nothing before exiting my macros. Nothing I have tried, or found suggested so far in the forum works.
I'm getting the error for the following piece of code.
Sub itconfandscratch() Dim Cn As ADODB.Connection Dim Server_Name As String Dim Database_Name As String Dim User_ID As String Dim Password As String Dim SQLStr As String Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Server_Name = "sturecord" Database_Name = "Scratch" ' Enter your database name here SQLStr = "SELECT stuname FROM dbo.sturec" ' Enter your SQL here Set Cn = New ADODB.Connection Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ""..............
The code thats generating the error is the following:
For Y = 8 To 131 TmpDate = Empty Range("A1").Value = "=" + Path + Sheet + "B" + CStr(Y) TmpDate = Range("A1").Value
For Langd = 1 To Len(TmpDate) TmpChar = Mid(TmpDate, Langd, 1) If Not TmpChar = " " Then TmpComp = TmpComp + TmpChar End If Next Langd
TmpDate = TmpComp TmpComp = Empty
If Len(Dag) = 1 Then Dag = "0" + CStr(Dag) End If
Macro continues before the Next-statement...
(it might not be good programming, but I think it should work). A little explanation to the code. I have a spreadsheet located on the intranet that has values I need in my spreadsheet. These figures are sorted by date, so I search for the date to find the right figures. The line Range("A1").Value = "=" + Path + Sheet + "B" + CStr(Y) works prefectly. I can see the value in my spreadsheet in the cell A1. its the next line that causes the error "Type mismatch". The value I get from the intranet spreadsheet looks similar to this: "1 Jul 2006 " (note all the spaces). What I do is just run it through a loop and cut of all spaces so it will look like this: "1Jul2006" to be sure I dont miss a space or anything.
Now to the funny part. The error only happens at runtime on the first go. If I chose to END the macro then and there, and then run it again from the top it works perfectly. If I set a stopsign to debug the code, it works perfectly on the first go too. Can anyone explain to me why that happens??
I know enough about programming to handle my syntax errors and so on, but I cant see anything wrong in this one...
Please note that this isnt the the entire code. I'm building a report on 12 diffrent spreadsheet. The main macro is almost 2000 rows of code, and it call other Subs too (because of limitations in the VBA-editor. A macro cant to be too big), so its impossible for me to post the entire macro...
I have to calculate the difference between the start time and end time of a job. The only catch is, how can I avoid calculating "out of hours" time. So, if a job goes from 9am to 9am the next day, I want it to avoid calculating between the hours of 23:30 and 03:30.
Another example is if a job goes from 02:00 to 04:00, I want it to avoid the tim between 02:00 and 03:00.
If there is a difference in days, so the job goes overnight, how do I take that into consideration also.
I've got a time difference from 8:00AM - 12:30PM as 4.30 I'm trying to get the minutes, .30, converted into a 6 minute increment, .5. Is it possible to do this and if so how would it be done? Below is a chart of how the time is converted from 6 minutes increments into decimal form.
i want to ask for a reason if a item is late where M & line is the time it should have left and N & line is the actual time it left.
TL is the difrence between the two times i want the input box to read " DRIVER DISPATCHED 30 MINUTES LATE PLEASE ENTER REASON" but it returns "DRIVER DISPATCHED -.11233543 E2 MINUTES LATE" how do i format this to show the difrence in minutes
TL = Range("N" & Line) - Range("M" & Line) late = InputBox("DRIVER DISPATCHED " & TL & " MINUTES LATE PLEASE ENTER REASON")
if I leave the inputbox blank I get an Excel error. I.e. it says that the formula I tried to type contains an error..... Is the Type:=8 not working properly here?
Dim Addr As Range On Error GoTo Problem Set Addr = Application.InputBox( _ Prompt:="Enter or Select a cell in the last record", _ Title:="Select Last Record:", Default:=Selection.Address, Type:=8) If Addr Is Nothing Then Exit Sub Edit: I guess I should mention that I am trying to retrieve a cell reference from the user via an input box. This cell reference will help me determine the last record in the spreadsheet.
In Excel 2003 I have made a model for evaluating co-workers. The model consist of a number of different questions regarding performance. At the top of the sheet, the user have to select som data from a drop down list, and depending on the answer (upward or downward), som of the questions in the sheets is hidden or shown. For this purpose I have created this string of VB-code in the sheet:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$5" And Target.Value = "Upward" Then Rows("20:41").EntireRow.Hidden = True ElseIf Target.Address = "$E$5" And Target.Value = "Downward" Then Rows("20:41").EntireRow.Hidden = False ElseIf Target.Address = "$E$5" And Target.Value = "" Then Rows("20:41").EntireRow.Hidden = False
Private Sub Worksheet_Change(ByVal Target As Range) If Not (Application.Intersect(Target, Range("G9:BF94")) Is Nothing) Then With Target If Not .HasFormula Then .Value = UCase(.Value) End If End With End If End Sub
However when ever I select more than one cell which already has text in it and press delete I get a runtime error 13 and the debugger highlights this line:
I have created a workbook for external users that allows them to track data on a monthly basis and submit to me. Each spreadsheet has a code to sort names alphabetically. Here is the
Sub Button53_Click() Dim i For Each i In [B10:K309] If i.Value = 0 Then i.Value = "" End If Next ActiveSheet. Unprotect Password:="xxx" Range("B10:AB309").Select Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.ScrollRow = 10 ActiveWindow.SmallScroll ToRight:=-5 Range("B10").Select ActiveSheet.Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub
Everything works great for the majority of users, but those who are using Excel 97 return a runtime error on the following line: If i.Value = 0 Then. This code was created in Excel 2000 since that was the software our office used.
Public Sub sendRow(myRow As Integer, destinationSector As Integer) '************************************************************ '1. Copy Row... Cols 2-7 '2. Find First Empty in new Sector '3. Paste Row on first new Row
Dim myBounds() As Integer Dim destRow As Integer Dim destRange As Range Dim cutRange As Range Dim y As Worksheet
myBounds = getSecBounds(destinationSector)
This subroutine is being called from the Worksheet_Change event, and should therefore have an active sheet.
I am trying to copy the info from one workbook to another workbook.
I keep getting the above referenced error...
Private Sub CommandButton1_Click() Dim filepath As String filepath = Range("A100") MsgBox ("File Appended") Workbooks.Open (filepath) Windows("CorrespondenceMaster.xls").Activate Sheets("Sheet2").Select Range("DesNo", "LocationPath").Select Selection.Copy Windows(filepath).Activate Range("A2").Select ActiveSheet.Paste End Sub
Is there a limit on the amout of pictures you can use with the statement me.pictures.visible? Reason being, I am trying to import 119 pictures into a spreadsheet. When the information is selected form a drop down list, it pulls up the 1 of the 119 pictures. I was able to get 54 pictures input. Everything was going great until the 55th. When it stared giving me an error... ______________________________________________ Runtime Error '1004':
Unable to set the Visible property of the Pictures class ______________________________________________
When I select Debug, it takes me to the line with Me.Pictures.Visible = False
Everything seems to work fine until I enter the 55th picture. If it is that, is there a viable work around?
Getting a error message when opening a workbook "Runtime error 1004" I dont know much about excel and the user explained it to me as best she could . Here is where the script bugs:
I have read the other posts on here and swapped, Dim timeinc As Interger to Dim timeinc As Long and Dim timeinc As Double but neither worked. They both make the calculation 0 and I can not figure out why. This macro has worked before and is failing only on this new dataset which I have attached as well.
I am solving this problem on a PC and the macro was built for a Mac but I do not think that should change much as when I run it on a Mac it fails the same
I have an application using two userforms. Following sequence is used:
1. Open Userform1 (with uf.show) 2. click on a commandbutton in userform1 opens a second userform on top of userform1. 3. Close userform2 with commandbutton on userform2 (userform2.hide) (I did not want the user to close the UF via the "X" )
Runtime error 402 : Must close or hide topmost modal form first
So, I changed my code so when the user clicks the commandbutton in userform2
userform1.hide (which is the topmost) userform2.hide
I have a script that usually runs, but all of a sudden gives me a run-time error 6: Overflow error. Basically I'm searching for certain criteria and copying or cutting and pasting lines from one sheet to another. Why I'm getting the error by the code below?
Sub teal() Dim i As Long Dim qq As Long Dim assignmentnum As Long[code]......
When I run the following code it keeps giving me a runtime error 1004.
Code: Sub ImportData() Dim fNameAndPath As Variant Dim wb As Workbook Dim cNextRow As Long Dim Ans As String NextRow = ThisWorkbook.Worksheets("Errors").Range("B" & Rows.Count).End(xlUp).Row + 1 cNextRow = ThisWorkbook.Worksheets("Compare").Range("A" & Rows.Count).End(xlUp).Row + 1
I have a bug on a macro that was working until yesterday. I did not write this macro and I cannot seem to fix it. The debug stops at the below command. The data it looks at seems to be in the right format so I think there is something else that is wrong.
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
I have a workbook with some Change Event code posted below. It executes fine 90% of the time; however, cells with dropdown lists create an issue. If I double-click one of the dropdown cells (to directly type a value), then while my cursor is in the cell, click the small arrow to display the list, a 50290 (Method Intersect of Object Global failed) error occurs and highlights the red line in my code. Any ideas what this is about and how I can avoid it? (I've also included my Selection_Change event, as that's the only other code on this sheet).
Dim OldValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("AD:AF")) Is Nothing Then Call Summary.summarize If Not Intersect(Target, Sheets("ProjectData").UsedRange) Is Nothing Then edited = Target.Row SingleVal Sheets("ProjectData").Range(edited & ":" & edited) End If If Not Intersect(Target, Range("K:K")) Is Nothing Then If Target.Value "N/A" Then While InStr(1, confirm, "y", vbTextCompare) 1 And InStr(1, confirm, "n", vbTextCompare) 1....................
I am currently trying to develop a code for calculating the value of an asian option using a binomial tree... In running the macro I get a run-time error 6 - overflow message. I have put the code below. note that the code is still under construction - in order to check it I have put in some hard-coded figures. Of course, these should be calculated in the model eventually. The bug is in calculating the "InterO1" value ...
Sub bereken_asian_call() sig = Sheets("Sheet1"). Range("B1").Value T = Sheets("Sheet1").Range("B2").Value N = Sheets("Sheet1").Range("B3").Value r = Sheets("Sheet1").Range("B7").Value div = Sheets("Sheet1").Range("B8").Value S = Sheets("Sheet1").Range("B12").Value K = Sheets("sheet1").Range("b13").Value alpha = Sheets("Sheet1").Range("B14").Value Dim St() As Double Dim F() As Double Dim O() As Double Dim NewAv1() As Double Dim NewAv2() As Double Dim Ffut1() As Double Dim Ffut2() As Double Dim Ffut3() As Double Dim Ffut4() As Double Dim den1() As Double Dim den2() As Double Dim InterO1() As Double Dim InterO2() As Double dt = T / N u = Exp(sig * Sqr(dt)) d = 1 / u pu = (Exp(dt * r) - d) / (u - d)................
I have written some code to perform a Vlookup for some data from another sheet but when i run the code it comes up with runtime error '438' "Object doesn't support this property or method".
Sub RAS_StockUpdate() Dim Count As Integer Dim SKU As Long Dim FileName As String FileName = ActiveWorkbook. Name Workbooks.Open FileName:= _ "\Hwyfile1publicRange TransitionRAS DatabaseRAS_Data_Export.xls" Windows(FileName).Activate For Count = 1 To 100 Range("B16").Select ActiveCell.Offset(Count - 1, 0).Select Select Case IsNumeric(ActiveCell) Case True...................................
I downloaded an excel file from a German student's site. With this file, it is possible to calculate very simply species turnover (I'm a biologist) activating a macro. When I run it, afetr answering lots of questions (which I think I answer well), it appears an "error run-time 9 - subscript out of range". In the download folder there were only two files: turnover.xls and instructions.pdf Here is the code. The debug highlights the bold line. .............................................
This is a function to add the ascii values of a string to give a single value. Initially I wrote this as a Sub routine and it worked fine, but when making it into a function, I get this error. From a little research it appears that you can not use a string as an argument for a function call, yet that defeats the object of this particular function.
The intention of this function is that it works on a name in a given cell and the value (an integer) that is produced is then displayed in another cell, or if using it as a formula, the cell the formula is in.
I simple wrote the work "Hello" in cell A1 the ascii values of these added ignoring any spaces give 500 which is correct.
