Find In Code To Search On Different Sheet Is Failing
Jun 26, 2007
The below code works well if it is on the proper sheet, if the active sheet is not active then it errors- Why? I think I have properly qualified the path to the desired sheet I wish to search on.
Error msg: Activate method of Range class failed
And how do I handle this?
Set Ws = Workbooks("ViewRenameDeleteFiles.xls").Sheets("Item Record List")
What = InputBox("Enter the Name You are Searching for its Record#", "Item Name Searching On")
I have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter.
Here is what is making this problem a little tricky:
- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.
- When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.
where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.
My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.
I have an Excel 2007 workbook with over a dozen user forms and plenty of code that I have mainly obtained from the web and tweaked to suit my needs. I am a novice with VBA, so not really up to writing anything but quite simple code.
My issue is I have some code set to fire on Workbook_Open that will save a versioned copy of the workbook, to the same network folder that the original resides in, with an incrementally increasing file name. the file is stored on a network, but access should not be an issue as I have full access and have no problem saving to this folder, also the event works fine up until I shut down Excel. I have also tried saving to My Documents to avoid the network issue, same result, worked fine as long as Excel is not closed, fails if I do.
It all works fine until I close down excel completely and re-open, where it then fails to work. There are other events happening in the same Workbook_Open sub that still work fine each time, so the sub is firing on open, but this one event fails. I get no error message at all, just no new file copy created.
The workbook is essentially doing the job of an Access database (I know even less about Access), I have a user form as a main menu and various other forms for various data entry and reporting tasks. I am exiting the w/book via a cmd button on the main menu (I've deliberately restricted users control, as many are not very pc literate). I have conducted numerous trials consisting of running the code from the VBA window, closing w/book via cmd button WITHOUT accepting the std save option and re-opening from Explorer window, closing w/book via cmd button WITH accepting the std save option and re-opening via Explorer and all worked perfectly over many sample runs. But when I closed Excel totally (Not just the w/book), created a desktop shortcut and opened from there, that line of code just doesn't seem to do anything, no error or hang or anything. The only way I can get it to function again, is to re-save the w/book (As either a new file with code edited to suit, or overwrite the original), and keep Excel open whilst only closing this w/book. It then functions perfectly again on opening.
I have enclosed below the Workbook_Open sub and some other subs that append to a user log on opening, these work fine all the time. I enclose the others in case they may have some bearing, as they are also fired from the Workbook_Open sub and show no issues at all.
Any suggestions gratefully accepted as I am struggling. As mentioned above, the ONLY part of the Workbook_Open sub that fails is the line "ThisWorkbook.SaveCopyAs newFileName". I have even added "MsgBox "The new FileName is: " & newFileName" immediately after it, and that displays new filename correctly. It seems to me to be hingeing around the SaveCopyAs event, but I don't want to assume that, being the novice I am.
I made a new class to make other code less redundant, but it isn't functioning. The class has a "workbook" and "worksheet" member, and these can be accessed through properties. The problem is that the properties don't actually seem to return anything, and no data can be accessed through them.
Private mActiveWorkBook As WorkBook Private mActiveWorkSheet As Worksheet
Property Get wSheet() As Worksheet wSheet = mActiveWorkSheet End Property
Property Get wBook() As WorkBook wBook = mActiveWorkBook End Property
'Sets active WorkBook Sub SetActiveWorkBook(ByVal wBook As String) Set mActiveWorkBook = Workbooks(wBook) End Sub
'Sets the activeWorksheet in the workbook. Sub SetActiveWorkSheet(ByVal wSheet As String) If mActiveWorkBook Is Nothing Then MsgBox ("Invalid WorkSheet selection - WorkBook not defined") Return End If
Set mActiveWorkSheet = mActiveWorkBook.Worksheets(wSheet) End Sub
uDate.wSheet. Range("A1").Value = "foo" ' Expected result - set Cell A1 in sheet testTab = "foo" ' Actual Result - nothing
Dim st As String st = uDate.wSheet.Range("B5").Value MsgBox (st) ' Expected results - bring msgBox of values of cell B5, this cell is not empty ' Actual Result - Empty Message box comes up
So - its fairly obvious to me that something is wrong with the properties. The members themselves are not null, I have verified in the debugger that the class members refer to actual sheets/workbooks, but the properties don't like passing anything out and show as "variable not set" in the debugger. How can I get this to work?? It works fine when I do not use the class, like such:
Dim wB As WorkBook Set wB = Workbooks("Book1.xls")
Dim wS As Worksheet Set wS = Worksheets("TestTab")
Dim st As String st = wS.Range("B5").Value msgBox(st)
But I would like to get the class and properties to work to save clutter elsewhere.
Code such as the one below carries formulas in columns 3 and 10 down to new rows when inserted. However, I need to allow a user to overwrite the formula in col 3 with any text value. But if the user overwrites the formula on the last row, and then manually enters a new record below, then the code in the last row of col 3 does not get copied down. Is there a way to search upward in columns 3 and 10 until reaching the last cells with formulas, and apply the formulas to the new cells in cols 3 and 10 on the new row? Or, is there a way to store the formulas:
=IF(A21<>"",B21,"") in column 3 =IF(B21<>0,IF(C21<>B21,A21&", "&LEFT(C21,2),A21&", "&LEFT(B21,1)),"") in column 10
Storing these somehow would be good if the user overwrites the formulas in the very top row of data (row 2).
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub On Error Goto CleanUp: With Target If .Value <> "" Then Application.EnableEvents = False .Offset(0, 3).FormulaR1C1 = "=RC[-1]=R2C3" .Offset(0, 10).FormulaR1C1 = "=RC[-1]=R2C10" End If End With CleanUp: Application.EnableEvents = True
The code below will locate and select cells containing Kenny. What I want to do is create a little search cells(C5) on my spreadsheet and have the code reference whatever is typed into the search cell. I'm sure it's a simple adjustment. I've tried changing the What:="Kenny" to What:="=C5" & What:=C5 all without success.
Sub find() Cells.find(What:="Kenny", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True).Activate End Sub
I have a number of worksheets with data in various rows, all rows of data have UID's. I want to create a search function, which returns the location of the of the UID. Or better still the cursor will jump to the cell where the UID is located. It's very much like the way the 'Find & Replace' function works.
E.g. If I type the UID of 1234 into cell A1 I want to be able to press a 'Find' button (which will be located alongside cell A1) that will search the entire workbook for 1234.
I have a workbook with one sheet (called "Pipeline") and another sheet called "Completed". The Pipeline sheet is used to keep track of all of the tasks that the team are working on, and then when the task is completed, it needs to be moved to the "Completed tab".
I currently have managed to write some code that looks for items marked "completed" in row G of the "Pipeline" sheet, then copies them over to the "Completed" tab, and then deletes the row from the "Pipeline" sheet.
There is one macro to copy the completed cells (called "Transfer"), and then another to delete the cells (called "Delete"). I then just have macro called "Clean" that just calls the transfer macro, and then calls the delete macro.
Often, there are 6 tasks marked complete, and only some of them will be copied over, but all of them will be deleted (a disaster).
I am working on an existing large excil file with over 60 sheets. What I want to do is create a search box on the first page, our "main menu." The search is for an product ID Number and then will open up the sheet in which that ID # is associated with.
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
I am using the Vlookup function to find a particular code in one sheet then give me the cost data that is associated with that code and put it into a similar cell in another sheet. It will work fine if I use (for example) A2 to E39 as my table_array. This would be fine if I only had 38 rows of data to look for the code on. However I have thousands of rows of data that need to be checked. Therefore when I change the table array to anything greater than E40, the vlookup will not work and it will only return a value of zero instead of the actual cost data. This is very weird and does not make sense that anything is wrong.
The only thing I can think of is that the vlookup function has a limit on the number of rows that it can look for data in. However, I did not think that it had a limit at all.
I'm making a macro to show or hide rows depending on whether or not a checkbox is checked. I'm using Wingdings font checkboxes, not actual form controls. The question: My macro can't find the last row of the used range on the sheet. This is in the sheet's code, set to fire when the selection changes.
Normally the line in blue works just fine, but it's not working here. In the attached workbook, the macro thinks the last used row is row 19, when it's actually row 21. What's going on?
I have this code attached to a button on the first sheet of a workbook with hundreds of sheets.
it is suposed to look for a cell that contains "SAY:" and then move one column to the right and make it a zero. It works on the first sheet but not on any other sheet.
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
I have a workbook that has several macros. When the workbook is saved, it cycles through and kicks off several of them.
This works fine, no errors. I have another macro that saves and then emails the workbook. When this macro runs, the same set of macros is launched due to the save method however it errors during one of the macros.
Basically what's happening is that the one macro (derivecountry) unprotects a sheet and inputs a data value. This macro works fine when run independantly but when it runs as a result of the email macr, the sheet does not get unprotected and the error results.
Here is the code line that runs to unprotect but doesn't always work
From this forum I've borrowed code and reused in my own CapEx approval form. The form is originated and goes through three people for approval. Everything seems to work ok on the form but it always forwards to the first approver rather than the next in sequence. I cannot see where the code is wrong.
This macro should copy all data from all worksheets and past them into the sheet named "Consol" however It is not looping and only pastes the one sheet.
Sub LoopThroughSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Formula FinalRow = Range("A65536").End(xlUp).Row Range("A2:U" & FinalRow).Copy
I'm finding that my add-ins that worked perfectly on Excel 2003 are not functioning on Excel 2007. One of the main issues seems to be loading any UserForms that use the RefEdit control. I get a message saying:
I have written the following code that is supposed to be running on opening a work book. The macro is being called via Application.Run "CreatePT" which is in the this workbook code in a Sub workbook_open().
The code run on its own is fine with no errors... It only bombs out when its called via application.run then its a run time error 1004? ...
Range Name = AssignDt RefersTo: =OFFSET('Raw Data'!$W$2,0,0, COUNTA('Raw Data'!$A:$A)-1,1)
I perform a routine in VBA which deletes unwanted rows of data from my worksheet. The problem occurs if row 2 happens to be one of those rows. It not only deletes Row 2, but it also deletes my Named Range.
I am trying to compare to values that a user inputs in a user form. It's ok if they are not the same I just want the user to know it. I am only including the part where the if statement is failing:
VB: If Me.VSLongQuant.Value <> Me.VSShortQuant.Value Then MsgBox VerUnBal If VerUnBal = vbNo Then Exit Sub End If End If
I have VerUnBal as Long and defined as:
VB: VerUnBal = MsgBox("The Long and Short option Qunatities are not equal." & Chr(13) & _ "Is this intended to be an unbalanced vertical spread?", vbYesNo, _ "Unbalanced Spread Alert")
I can provide more info as necessary. There are two problems 1) the message box is appearing whether the numbers are equal or not, and 2) clicking No in the message box does not cause the Sub to exit.
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
Here is an example of my spreadsheet: Excel example.xlsx
I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.
I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.
Create a macro button in 'Spreadsheet 2' that searches 'Spreadsheet 1' for updated information specific to a certain criteria and adds it to a new row in 'Spreadsheet 2.'
There are three sheets
Prospects (where all original data is entered)
Actions -Bob (Bob's new Prospects are added to this sheet)
Actions -Frank (Frank's new Prospects are added to this sheet)
So in this example Row 6 in Prospects (Constituents, Rating, Manager and Solicitor would be added to ACTION - Bob's sheet on Row 5 and Row 8 in Prospects (Constituents, its Rating, Manager and Solicitor would be added to ACTIONS - Frank sheet on Row 5
Here is the example spreadsheet - Prospects and Actions.xlsx
I have included the table. What I want to do is in a user form pick a county ie Devon, Then by clicking "go" all entries that contain "Devon" are then copied to a " sheet 2" I don't want to use the Ctrl-F
