I've got a macro called "onundo" that runs when Ctrl-Z is pressed, assigned using "application.onundo" My problem is that after the macro runs I need to set the undo button again with application.onundo, but it doesn't work. If I put application.undo into the immediate window after the macro runs then that works - am I doing something wrong?
View 4 RepliesWe're gradually moving over to Excel 2007 (from 2002) at my workplace.
My problem is that I need to run macros on both versions but I'm having trouble with the SaveAs method.
I have Excel 2007 set to save automatically in Excel 97-2003 File Format but this doesn't seem to apply to files saved using VBA.
My original 2002 code is as follows:
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:ReportsNewFile.xls"
and 2007
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:ReportsNewFile.xls", FileFormat:=xlExcel8
How can I get it to switch depending on the version it's run on? Unfortunately Excel 2002 doesn't recognise '=xlExcel8' so I don't think I can use an If/Else statement on the version number.
Below is the code in which the macro is showing error
Run time error '1004'
Application-defined (or) Object defined error
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,
View 4 Replies View RelatedI have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
with opening a workbook at a specific time and/or running a macro at a specific time.
View 12 Replies View RelatedI realy don't understand the FileExists method at all. My VBA Editor doesn't seem to recognise the keyword FileExists ?
Allot of examples will write FileExists(Dir(sFilePath)) in their explanation but I don't know what the Dir is referring to. Does this mean the directions to the file or is this referring to the directory and Dir is actually a keyword for direcory?
I don't think I've found a single simple example which explains this properly?
I have some code which renames files. The key line being
View 2 Replies View RelatedIs there a way to use the find method on 1 row only?
View 2 Replies View RelatedI'm having some trouble fully understanding the Intersect Method. The help files use examples that check if a range Intersects another, if the do then display a message box; I think I understand that.
The usage I don't understand is like this
Set MyRange = Range("C:C", "D:D", "E:E")
Code here
Intersect(CopyRange, MyRange).copy
I am trying to copy some code from the Excel help directory but I am getting an error saying "Compile Error, Can't find project or library".
What am I missing here?
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If
What's the easiest way to return the address of a cell without the dollar signs?
Example:
msgbox activecell.Address
' Returns $D$8
I'd like to see the address written as "D8" because I will be autofilling later.
I am trying to invoke an excel VBA macro from excel from a button or in some
way directly by the user (that is by not having to invoke macro from excel menu)
that can be invoked from a Macintosh or PC version of excel. That is, it should
not used active X controls but in some other way that is best practice. Is there
any way to invoke a macro by button or menu item that is cross-platform?
I havent work with excel for years and now I have to do this as soon as possible:
There is a Donto's formula for calculating votes.
I have a conditional formatting or some other method (open to suggestions) which will identify those line items which need his attention.
I'm attaching a spreadsheet. Note that my items which I'd like flagged are not necessarily one right after the other in the column - i.e. there will be some which are skipped and not needed to be 'rated'.
I have a range object representing a data set, and I want to return only the first row of data from the range. The range starts at cell A2:
21 22 23 24 25
31 32 33 34 35
41 42 43 44 45
I.e. the value 21 is the data in cell A2 (row 2, column 1) and so forth.
'Given that WS1 is a worksheet object representing Sheet 1
'Given that WS2 is a worksheet object representing Sheet 2
I am running a vba code to add about 200 sheets...my code generates a sheet in one file and then pastes it in another file....after generating nad adding 38 sheets i get the error copy method of excel failed
i tried clearing the clipboard and resuming the code but that didn,t work
I am trying to sum the cells in a column from a variable starting row to a variable ending row. My problem is that I can not figure out how to use the "Range" method with variable values.
Below is what my code would look like if I was summing from I2 to I6 (hard coded)
I have a working macro that duplicates the active worksheet with values only. It basically cleans out formulas and data validation from the data. The code is:
View 2 Replies View RelatedI need a macro to run at a certain time on Wednesday's, and then a certain time on the other days.
I would like to use a cell to reference that time, but right now, I am just hard coding it, but I an not get it to run.
Ultimately what I am looking for is something like this: ....
I am going through a large block of data with formulas, where I am selecting each cell and manually highlighting the formula and pressing F4 to sorround the cell letter calls with $, example $A$11. I am doing this so I can ultimatley copy the formulas as a group into another group of cells with screwing them up. This is very time consuming so I am looking for an improved method, possibly using vb code?
View 9 Replies View RelatedI need to apply filters based on 2 criteria
Filter1Dropdown SelectionFilter2Dropdown SelectionHeader1Header2Header3Header4Header5Header6
Depending on what is selected in Filter 1, lets say it applies to column F and i get a data set
Now with the filtered dataset of Filter 1, I should be able to apply Filter 2 or vice versa.
Also at the end, I need to release all filters so that i can see the entire data set.
I know I must be missing something basic, but why is this code assigning a value to "NamePosition", instead of a range:
RightName = Sheets("WorkingSheet").Cells(1, 1).Value
Sheets("Tracker").Activate
Set StudentTbl = Sheets("Tracker").Range("A1:" & "Z" & NumStudents)
Set NamePosition = StudentTbl.Find(What:=RightName, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
I thought the find method returned a range?
Im trying to use the transferspreadsheet method to load some data in an access table, but using my current code, it gives me an error, saying that the file is already opened by another user, or that i need permission to view it.
that it is open, is true, since I'm loading from that spreadsheet.
this is the code I have now:
Sub LoadSheet()
Dim accappl As Access.Application
Dim strpathdb As String
Dim strpathxls As String
Dim myrange As Range
Set myrange = Range("a32").CurrentRegion
'path van de database
strpathdb = "X:credepSTRAT_PLANStratPlan.mdb"
'path van de upload file
strpathxls = ActiveWorkbook.FullName
Set accappl = New Access.Application
accappl.OpenCurrentDatabase strpathdb
DoCmd.TransferSpreadsheet acImport, 3, "Tbl_Growth_Metric", strpathxls, False, "myrange"
When I protect my sheet, the module I have no longer works: an error message appears saying "Method failed..."
I searched around and found that if i used Me.Unprotect and Me.Protect at the beggining and end of the code, the problem would be solved, except that it isn't. After that, another message appears saying "Invalid use of me keyword"...
I am having trouble assigning the range given by an inputbox method to the variable targetcells, and subsequently selecting targetcells. any help would be most appreciated.
Dim i As Integer
Dim CopyCells As Range 'the cells into which links will be copied
Dim TargetCells As Range 'the cells that copycells will be linked to
Dim CopyAddress As Variant 'the address of the copycells
Dim TargetAddress As Variant 'the address of the targetaddress
i = 0
For i = 1 To 100
Set CopyCells = Application.InputBox("Click on the cells to copy", "Automating copying links", Type:=8)
Set TargetCells = Application.InputBox("Select the target cells", "Automating copying links", Type:=8)
TargetCells.Select 'this line doesn't work for some reason (why?)
TargetAddress = ActiveWorkbook.Name 'this and the above lines are the ones I am having most trouble with - assigning the name of the targetcells workbook
I am having trouble using teh find method. I'm using it to search for a string in a column, then give me the row number, which is fine when the string is found, but when it is not I get an error of "variable not set." I'm pretty certain it is returning void, but how to I capture that? Here's the
voucher_row = Worksheets("Table").Columns("D:D").Find(voucher, LookIn:=xlValues, LookAt:=xlWhole).Row
"voucher" is a string.
I need to assign a procedure to the OnKey method, but VBA does not let me specify arguments with it.
View 4 Replies View Related