I thought I had this working for me but I think I made a change some where that is messing it up. Also my workbook is getting so big that it is getting harder to test each piece. and as I don't exactly understand the "with" command I am hoping that someone can look at this real quick and see where I messed up. so here is the
View 3 RepliesIm am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)
application.FileSearch.NewSearch
application.FileSearch.LookIn = Workbooks(ActiveWorkbook.Name).Path
application.FileSearch.FileType = msoFileTypeAllFiles
application.FileSearch.SearchSubFolders = True
application.FileSearch.Filename = "Zone Selling*.xls"
application.FileSearch.MatchTextExactly = True
application.FileSearch.Execute
filecount = application.FileSearch.FoundFiles.Count
For i = 1 To filecount
Worksheets("Run").Cells(i, 1) = application.FileSearch.FoundFiles(i)
Next i
For i = 1 To filecount......................
But it won't list my files which is how the macro was running, it was returning a list of the files in the folder and then running them based on the path returned
I have created worksheets search box and want to use Trim Function to avoid spacing error when user enter the sheets name they are looking for and also sheets name in the Activeworkbook.worksheets.name
However the trim Function seems does not work
[Code] .....
Have the following code:
With iSheet
i = .Range("A" & Rows.Count).End(xlUp).Row
j = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range("A1", .Cells(i, j)).ClearContents
[Code] .......
If I use F9 to pause the code before the sort part in blue, then the data imports into Excel from the database and then subsequently pressing F5 sorts the data as required.
However, if I run the macro in a single pass, the data does not sort. Why it's not sorting as expected and how to make it sort as required?
I have a formula to find if the Symbol "!" is contained in the a cell. And i want it to input "Undercut Due to Trim Edge" if there is a "!" symbol and blank if there is not. What did I do wrong?
=IF(ISNUMBER(SEARCH("~!",AL57:AV57)),"UNDERCUT DUE TO TRIM Edge","")
I have 2 workbooks. 1 is where data is entered called wksPB, the other is like a database workbook. The wksPB has data to be feed from column B to F and there is a dropdown.
what im looking to do is, if the combobox value is selected as Decline then it should display a messagebox that reference value in cell B is denied. if combobox value is Agreed then the macro should search the database workbook for the text entered in column b of wksPB and then copy data of C:E of wksPB to H:J of database sheet where that text is found and also the approver name in wksPB C24 to the approver cell of where that text was found. I've reached till finding the text but what I get my head twiting on is how to copy the text from wksPB column B:F to database sheet column H:K. Im attaching the sample workbooks and the code where ive reached till.
[Code] .....
Attached Files
Replacement Records - 2014.xlsx‎
forum file.xlsm‎
I have created a spreadsheet that will show me where people are working on what day, etc. however i want to be able to filter by week to create a list of say 2 particuar shifts - in this case "syl ld" and syl n so that the spreadheet would show the people who are working these shifts and I could print out. I have tried Multiple Filters and Advanced search but cannot achieve what I require
View 3 Replies View RelatedI have a workbook with a ton of links to other tabs, but all links and tabs are within the workbook (none to other documents). I've found that a lot of the links are no longer working. This document is only a month old, so it's not outdated or anything. i find myself having to go back and re-link everything. This is a big file...i understand if i have to re-link everything once, but i'm worried that the links will "break" again.
does anyone know why these links within a workbook are not working? is it something i have to fix in the Options?
I'm in a workbook, and I want to look up a name. CTRLF or clicking on the binoculars both bring up the expected dialog box, but when I populate "Find What" and either hit enter, click Find All or Find Next, nothing happens.
The only thing I can think of is that I created a macro to function in one sheet only, then saved this workbook as .xlsm. But that doesn't seem right.
I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:
View 14 Replies View RelatedI am hoping someone can help. I am new to writing macros and have the following in the this workbook page. When opening the macro does exactly what I want and expect, but does not do the "before close" part. If I select the macro in editor and run it, it does just what I want, so dont understand what is going wrong. Do I have to put in some kind of prompt, or manually run this before close - preferably I would like it to just run.
secondly, I have password protected the workbook, but have to put the password in the macro, is there any way of making this invisible to anyone who then reads the macro....
In R1 I sometimes place a cell reference that I want the workbook to immediately go to when it is next opened. I have the following code which does not work as expected (Ignore the necessary .Select parts):
Code:
Private Sub Workbook_Open()
Sheets("MSCI Asia Data").Select
Range("R1").Select
If Not IsEmpty(ActiveCell) Then Application.Goto reference:=Range(Range("R1").Value), scroll:=True
End Sub
Currently R1 contains the value R91 but when the workbook opens, it is not going to the cell R91 in the required worksheet.
is there any way to work with excel workbook while in another excel workbook userform is running
View 1 Replies View RelatedI have a user who is trying to use <EDIT><MOVE OR COPY SHEET> to copy a sheet to an existing workbook. She has them in the same directory and has them both open, but when she tries to copy a sheet from either of them to the other, the only workbook listed in the "TO BOOK" drop down menu is the one she is in. NO other workbooks show up as an option for her to copy to.
View 12 Replies View RelatedI am using this formula in Product Balance Update.xls,Tsuchiura worksheet that update data from A-Tsuchiura.xls, INVOICE worksheet.
When both workbooks are is opened, all linking are fine.
But upon A-Tsuchiura.xls is closed, my linking in Product Balance Update.xls,Tsuchiura worksheet will shows #N/A.
I had tried many ways for sumproduct
1) =SUMPRODUCT(--('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3),--('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))
2) =SUMPRODUCT(('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3)*('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))
I had read through this forum that sumproduct should work in closed workbook but I still cant get it why this error ocurr..
Please refer to attached to have a clearer picture of error
I have a workbook with 25 sheets containing metric information as part of a performance management model. Fundamental to this is the visual success or failure of each of these 25 sheets which I've highlighted by setting the worksheet tab colour accordingly. The code to achieve this is detailed below. This code is triggered by the Worksheet_Change event at the workbook level and works fine in single user mode. When the workbook is shared however, an error 1004 is generated.
Private Sub Worksheet_Change(ByVal Target As Range)
For Each c In Range("PassFail")
If c.Value = "Fail" Then
ActiveSheet.Tab.ColorIndex = 3
ElseIf c.Value = "Pass" Then
ActiveSheet.Tab.ColorIndex = 4
End If
Next c
End Sub
I am attempting to save a workbook when it opens, but when the code is run, it is not saving. For the purposes of finding the problem with the code, I have simplified it down to the following:
Code:
Private Sub Workbook_Open()
Range("C3") = "HEY"[code]......
I am using Excel 2010. I am using a shared workbook that was originally created in Excel 2003. This workbook has a command button that is supposed to be clicked to open a userform. However, I cannot even click on the button when opening the shared form in Excel 2010. When the form is not shared, the button works fine.
I have no problems with the button when opening the shared form in 2003 or 2007.
Is there anything I can do to make the command button work while the form is shared and while using Excel 2010?
I have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.
My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.
I am trying to develop my workbook design for my employers.So far i have already added a search function using a command button and text box to each worksheet and a toggle button to find and hide/show rows users dont need automatically with great success.
Here is the code i have adapted so far. Its not working correctly and i cant seem to figure it out. The command button and textbox will sit on the first worksheet and needs to search for a name (example: John) in the workbook and either display which sheet the name is found on or jump in turn to each name until you reach the correct sheet.
Do i need to set a range for the variable (ws)?
Private Sub CommandButton1_Click()
Dim search As String
Dim ws As Worksheet
Dim Answer As String
search = TextBox1.Text
On Error GoTo ErrorMessage
Application.EnableEvents = True
[code].....
I've had a look at various coding to create a macro to search excel.
The issue I have is several work sheets within the work book but they're always inactive unless opened. From the 1st work sheet I've got menu buttons that open a specific worksheet, then there's a button to click to take user back to 1st worksheet and close the 1 they were looking at and making it inactive.
Following code is what I use to open and close sheets:
[Code] .........
The above works fine for opening and closing.
But if I use Excel's standard CTRL + F search facility and change options to search workbook, it doesn't work because every other sheet other than 1st sheet is inactive.
I have a simple spread sheet that I made that takes the first letter of a name entered into cell (A1), combines it with the last name entered into cell (B1), and inputs that combined information into cell (E1).
It works how I need it to for the most part but... I need Excel to look at a completely separate workbook (containing four sheets), and search the entire workbook for exact matches of the combined information in (E1). If it finds a match elsewhere in that workbook I want it to add a 1 to the end of the combined name. I basically want it to increment the number until it does not find one that already exists in the other workbook.
Example: Cell (A1 contains Bob), Cell (B1 contains Smith). Currently Cell (E1 would display bsmith) - I need for Excel to search the other workbook, and if it finds a bsmith anywhere in that workbook add a 1 to the end making it (bsmith1). If it finds (bsmith1) increment it to (bsmith2) and keep going up by one number until it does not find any other matches. The other workbook is located on a mappedshared network drive.
I'm trying to write a Macro that searches all non-blank worksheets of multiple workbooks in a folder for any IP addresses contained within then returns them all to one master spreadsheet.
I've already come up with the code to open each workbook from the master. What I really need is some direction for how to go into each non-blank worksheet and search each one, placing all IPs that are found into a single column in their respective worksheets, say column D for example.
Since I'm not looking for a specific value I assume I'll need some wild card like *.*.*.*
I have two workbooks, WorkbookA and WorkbookB. Consider WorkbookA as the "summary" workbook where all of the data is presented in a simple manner. WorkbookB has about 100 worksheets (with each worksheet referring to a specfic job number ex. 1234). All of the sheet names of WorkbookB are listed in cells A1-A100 of WorkbookA.
I need a way to do the following:
1. While in WorkbookA, search WorkbookB for worksheet "A1"
2. After the correct worksheet is displayed in WorkbookB, I will be doing a series of vlookups in order to gather the data.
3. Repeat steps 1 and 2 for all 100 job numbers (cells A1-A100)
how I can achieve step number 1, I'd really apreciate it. I created a macro in the past to search WITHIN a workbook for a specific sheet, but I've never made one to search thru another workbook based on a specific cell value. The code I used to search within a workbook is shown below.
PHP Code:
Sub GetSheet()
Dim SearchData As String
SearchData = InputBox("Enter assembly number (including group)")
If SearchData <> vbNullString Then
On Error Resume Next
Sheets(SearchData).Activate
If Err.Number <> 0 Then MsgBox "Unable to find sheet named: " & SearchData
On Error GoTo 0
End If
End SubÂ
way of being able to search ALL the sheets in one go on a workbook? Going through all the sheets maybe one by one, i cant fathem it at all.
View 9 Replies View RelatedI am looking to write a macro that will search for keywords on a sheet, on a workbook at a specific path, and would like some help getting this macro started...
It would get run from "WorkbookA", and would look at "WorkbookB" (wherever it is) and find the cell containing the keyword, where the cell value would be the output. I would really prefer that it did not open Workbook B while this macro is run, if thats possible.
I am just wondering if there is a way i can search an excel workbook for a particular reference number. eg
name reference money
dave 60056835 £20.00
clare 50065755 £30.00
If i want to search for Ref 60058635 i just want to type it in and excel will the search the workbook and direct me to the correct page and column.