Sub StartCycleB() ActiveWorkbook.UpdateLink Name:="c:/a/b/c/test.xls", Type:=xlExcelLinks RunWhen = Now + TimeSerial(0, 15, 0) Application .OnTime RunWhen, "MyMacro", , True End Sub
Works fine. Its updating and refreshing a file every 15 minutes. When a cell in the test.xls changes to TRUE, my sheet picks it up and changes to TRUE also. So here is the problem: Once my sheet changes to TRUE, another macro activates in my sheet. This macro will automatically activate every 15 minutes if value is TRUE. BUT the problem is i ONLY WANT TO ACTIVATE THIS MACRO ONCE a day IF the value in my sheet is TRUE. So in summary how do i activate a macro based on a TRUE value on a cell, to happen ONCE a day and then stop?
I am trying to use the application .ontime procedure to run a class method, but having a little trouble with what to use as my procedure name. Do I have to include the name of the class, or the name of the instantiation of the class, or what? Here is kind of what I have in the "myClass" class module:
Option Explicit Private WithEvents m_cmdButton As MSForms.CommandButton Private m_iTimer As Double Private Sub m_cmdButton_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) ' Set the time with the long delay m_iTimer = Now + TimeSerial(0, 0, I_TIMER_DELAY_LONG) ' Start the timer Call Application.OnTime(earliesttime:=m_iTimer, procedure:="ClickAgain", schedule:=True) End Sub
Private Sub ClickAgain() Call MsgBox("ClickAgain successfully called.", , "Boo yeah!") End Sub
I have checked and the MouseDown event is being registered: it will work if I use a procedure in a standard module rather than the class method"ClickAgain". (I am only including the code relevant to the "ontime" procedure. My actual class has other methods to deal with setting the control source for the command button & everything.)
I have 2 macros, each called by the other to keep updates fast as per below,
Sub Macro1() Cells.Replace What:=",29", Replacement:=",30", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Application .OnTime Now() + TimeValue("00:00:01"), "Macro2" End Sub
Sub Macro2() Cells.Replace What:=",30", Replacement:=",29", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Application.OnTime Now() + TimeValue("00:00:01"), "Macro1" End Sub
I need to create a separate macro I can run to permanently stop these two cycling. And allow me to use Excel. Then, when I wish for these to cycle again, I simply run that macro.
my wonderous spreadsheets have had a number of errors on them. One of them seems that the "autosave" VBA that we have been using seems to be hidden in memory. By this I mean that if I am working on this book and close it, on occassion the workbook tries to open itself again (it prompts for the password). Therefore I think something gets stuck "in memory" but I am not sure why it would act in this way?
The code is as follows:-
Public g_blnDate As Boolean
Public vartimer As Variant Const TimeOut = 60 'in minutes
Sub Salva() ActiveWorkbook.Save Call Tempo End Sub
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
I have a problem with a macro that runs on a command button. When I click the button, I get a 'Run Time Error 1004, Paste mathod of worksheet class failed' The code is as follows:-
The procedure below worked quite happily for two months. Now it (usually, but frustratingly not always) fails, with the following error on the asterixed line:
Paste Method of Worksheet Class Failed
If anyone has the inclination, please could you put me out of my misery and suggest what is going wrong, as I have been trying on and off for two weeks to rectify this. What disheartens me is, as I say, I have had no problems with this procedure up until a couple of weeks ago. It takes the ordering info for a particular day and pastes it into the correct day's sheet on the invoicing spreadsheet. Sometimes, when I re-run the procedure it then works......???
Sub Transfer_To_Invoicing()
Dim answer As Variant, active_workbook As Variant day_num = Range("a127").Value answer = MsgBox("Do you want to transfer " & Weekday(day_num) & "'s figures to the INVOICING SYSTEM now?", vbYesNo) If answer vbYes Then Exit Sub answer = MsgBox("Have you updated the values from the despatch sheets?", vbYesNo) If answer vbYes Then Exit Sub Range("a1:dj129").Copy active_workbook = ActiveWindow.Caption For Each w In Workbooks.............
I have a UserForm (and associated code) to locate a specific entry in Column 'A' of a spreadsheet and insert a date, initials, and hyperlink to another file.
The basic code works fine, but did not account for the user entering a Job# that was not in the system, so I added an If/Else to account for this... The additional code took the If (Job# not found) route every time... Following many hours of searching this site and variations of the error handling code, I still can not get it to work.
So I'm asking for your assistance to point out why the error handling (based on other successful code found here) is not working for me.
'Insert Link Private Sub CommandButton3_Click()
If TextBox1.Value = "" Then MsgBox "Please Insert a Job#" Exit Sub ElseIf TextBox2.Value = "" Then MsgBox "Please Insert Quoters Initials" Exit Sub ElseIf TextBox3.Value = "" Then MsgBox "Please Insert Quote Date" Exit Sub
The 'Find' part of the code is working, as the correct cell is selected after running the code... but it gives the error message, instead of executing the rest of the code...
If I remove the 'On Error Resume Next', it stops on the find block, where on inspection FindR = Nothing.
So if FindR does = Nothing, how did it manage to select the cell?
Asking for a row value to be returned if a string is found to exist in the sheet. Works great if it finds the value but I get the following error when the string isn't found:
Run Time Error '91': Object Variable or With block variable not set.
Concept code follows. The commented strOCNumOF line contains the value that's found on the sheet. The uncommented line contains a value not found.
Sub find_test1() Dim intFoundOnCur As Integer Dim strOCNumOF As String
The error is: Method 'Range' of object '_Global' failed Line it fails on:
Set r = Range("myRange")
Sub LearnCells() Dim r As Range Dim n As Long Set r = Range("myRange") For n = 1 To r.Rows.Count If r.Cells(n, 1) = r.Cells(n + 1, 1) Then MsgBox "Duplicate data in " & r.Cells(n + 1, 1).Address End If Next n
End Sub Questions:
1) 1st Dim statement, is this valid? I still get a little unsure- in the data type lists in the help file, the list file does not list things like Workbook, Worksheet Range. 2) Why is the error ocurring?
The macro is timing out on the ActiveSheet.Paste entry...The funny thing is that a number of us can get the macro to work and several of us are getting this error...I am thinking that it is a setting in excel that is causing this...
I encounter a runtime error '1004' if the "Invoices" sheet is not selected when I run this procedure. The last line of the code is one which is highlighted when I debug.
Sub ProcessData() Dim aiOldRows() As Integer, aiNewRows() As Integer ' Arrays of new/old rows Dim rngRaw As Range 'Data entry area Dim rngInvoices As Range 'Invoices range Dim rngOpenPoint As Range 'Top-left corner of data entry area Set rngOpenPoint = ThisWorkbook.Worksheets("Data Entry").Range("a3") Set rngRaw = Range(rngOpenPoint, rngOpenPoint.End(xlDown).End(xlToRight)) FindNew aiOldRows, aiNewRows, rngRaw InvoiceSequence aiOldRows, rngRaw Set rngInvoices = Range(ThisWorkbook.Worksheets("Invoices").Range("A2"), _ ThisWorkbook.Worksheets("Invoices").Range("A3").End(xlDown).End(xlToRight)) rngInvoices.Sort Key1:=Range("M2"), Order1:=xlAscending End Sub
You'll notice that there are two other procedures (FindNew & InvoiceSequence) being called by this procedure. I don't think those have anything to do with the error, but I can provide the code for those if needed. Oh, and one other secondary question. To declare the ranges rngRaw & rngInvoices I pick the top-left cell of the data and then do:.....................
If InStr( Cells(i, 3).Value, "Other") > 0 Then _ Cells(i, 3).Replace What:="Other", Replacement:=Cells(i, 4).Value
This seems to work fine for the most part. However, if the value in Cells(i, 4) is too long, I seem to get a Run-time error '13': Type mismatch. Is there any way to rework this code so it can replace even if the string in Cells(i, 4).Value is too long?
I have searched the Forum for help on this error 91 but still cannot figure this out.
Below is a format macro I created. I have several ' Find' routines in here and at the beginning of each one I give it an 'On Error GoTo' type statement. This seems to work fine until it gets to a second error. I have moved various 'Find' routines around and it doesn't matter which order they are in, if there are two things it can't find, it gives the error 91 on the second.
Sub Format_SFDC_Detail_Reports() ' ' Macro to format salesforce.com reports with details. rguest '
On Error Goto errorhandler
Msg = "Do you want to format this report for Landscape (Yes) or Portrait (No)?" Style = vbYesNoCancel + vbQuestion + vbDefaultButton1 + vbSystemModal Title = "Format Report as Landscape?" response = MsgBox(Msg, Style, Title)
If response = vbCancel Then 'Exit the routine Goto last_line: End If If response = vbYes Then format_style = "Landscape" End If If response = vbNo Then format_style = "Portrait" End If
I have a macro that opens a specified woorkbook that changes every month. There are formulas which are pasted to range I1 of the new workbook to calculate the totals on this sheet. Everytime i run the code though, I get an error that says "Select method of range class failed" and Range("I1").Select is apparently the error.
I'm trying to set the print area on two sheets in the same workbook and it is returning Method 'Range' of object' _Global failed error every time and I can't figure out what code to change to make this error stop happening. here is my
Why I'm getting the above error when I try to copy and sort data into a workbook?
I'm using this:
Code: Sub GetData1() Dim SaveDriveDir As String, MyPath As String Dim FName As Variant SaveDriveDir = CurDir MyPath = Application.DefaultFilePath 'or use "C:Data"
[Code] .......
To import the data and then these lines to copy the unique records to a range:
Macro has been working fine for ages suddenly have received this consistently?
It's not debugging to allow me to see the route of the problem.
i've googled and seen it's normally a use of select but can't see any issue in my code or understand why it would work for months and now stop.
note: also after i click Ok on the error the macro seems to continue running and data is pulled through. ...but this message comes up every time so it's questioning the reliability of the data it's pulling.
Check Personnel Number in Data Tab with Personnel Numbers in Insert Tab. If they match copy that row from Insert Tab and paste it into the next available row in the Moves Tab.
This line fails, is Method Range Global error.... What is wrong w/ this, and how should it read?
If Range("D", i).Value = "Need Parent" Then 2nd Question- The following line is my normal method in doing a simple For Next Loop. What is the difference between using this one, looping through the cells and the first one referring to a Range for the loop?
I start in my "Action Plan for For Single Market" worksheet and if I click on the Command Button I then want to read in a couple of variables, and jump to a second worksheet (called "Market Action Plans") where I then want to copy a range, and then paste it in another area in that same sheet.
I thought I had worked out what to do, but when I execute I get a "run-time error 1004" when the macro gets to the "Range("Updated_Results").Copy" line. Excel describes the error as "Method 'Range' of object '_Worksheet' failed'" - which might as well be a foreign language as far as I am concerned!
End Sub I am an occasional macro-creator and every now and again, I come across this type of problem where I can't get variables to be reconized, etc. I think it's because I don't understand enough about Private versus Public, and how to declare variables.