I 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 RelatedI'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 Relatedwith opening a workbook at a specific time and/or running a macro at a specific time.
View 12 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 RelatedI am going through various excel sheets looking for certain text on them. not all of the sheets will have the text.
I have been using
Cells. Find(What:="FindMe", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
and it works fine, if the text is on the sheet. When the text is not on the sheet, how should I handle this.
I have tried On Error and IsError variations, but could use a little guidance
I am attempting to create a double-entry accounting system and am having trouble with my Transaction class (clsTransaction). As each transaction will consist of at least two entries, I have included a Collection in clsTransaction that will accept Entry objects (clsEntry) as items. The Add method appears to go ahead without a hitch, but when I output the values from the Entries I get the same values for all items in the collection. Here is the code I've been working on.
The Transaction Class
clsTransaction - (Class Module)
Option Explicit
Dim mcolEntries As Collection
Dim msDescription As String
Private Sub Class_Initialize()
Set mcolEntries = New Collection
End Sub
Public Sub Add(eItem As clsEntry)
mcolEntries.Add eItem
End Sub........................
In each VBA book I've read the authors will write their code like this:
nStart = UBound(GenArray1)
nEnd = UBound(GenArray2)
Redim preserve NewArray(nStart To nEnd)
Instead of the less line method of:
Redim preserve NewArray(UBound(GenArray1) To UBound(GenArray2))
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 Replies View Relatedcurrently i have this:
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?
