Am a newbie in excel macros. pls i need assistance as to how to create a macro that searches a worksheet for a particular word. i wrote something like below but am having problems in it.
Private Sub CommandButton1_Click()
Dim word As TextFrame
If word Is Not Empty Then
For word = "aaaaaaaaa" To "zzzzzzzzzz"
ThisWorkbook.Worksheet.Find
Next word
End If
End Sub
transferring data from a worksheet (Passdown Report) to another worksheet (Data Base) located in the same workbook. In the source worksheet (Passdown Report) there are 2 cells (B2 and D2) in which I would like the data to be transferred along with the data from B4 to AQ33. All the cells contain a formula which I want to stay after the information is transferred to the target worksheet (Data Base). This will be a daily transfer to the target worksheet (Data Base), so the macros should also identify the next available open row to transfer the data to.
View 1 Replies View RelatedI have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI would like to create a macro which will copy the names of the files and folders of a chosen directory into a spreadsheet, effectively creating an index. I don't know how to go about this or even if it is possible. I'm trying to avoid having to do it manually as the directory in question spans 1500 folders...
View 9 Replies View RelatedI want to create a custom tool bar for a work book but when I right click in the unused part of the tool bar area the only options I get are customize Quick access tool bar and customize the ribbon.
View 3 Replies View RelatedI have a dynamic set of data, loaded from a server. The columns are FIXED. I will attach a sample of how the data looks.
site_code
report_goup
asm_week
[Code].....
I don't know how to attatch a file here, but the first row is the title row, under it are the values. The columns here are fixed. What i NEED to do, is somehow create something that will make a seperate graph for every different PLATFORM , showing the FPY chaging over time. Time here is the weeks which will always be a rolling 12 weeks. I'm thinking that i need to use MACROS to do this, but i'm not sure where to start.
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies View RelatedI am new to using Macros and have not had great progress building a macros to make my life easier.
What I am trying to do
-In my main database tab 'FW Telecom' Use column A ( a set of numbers) to create a new tab based on that number, with a 'FW' infront IE ( column A shows 11, i want the tab to read FW 11)
-I want to create one for every number in the column
-Then I would like to copy my 'Template' tab to each new tab.
-From there I would like to fill in the information in the new tabs from the main database tab 'FW telecom'
-Since they will all be copies of the original template, i figure i can just get it to pull from the first column A and then fill in the info.
Not too much in theory going on here, just take my database and move the pertinent information to a user friendly look which I set up in 'template'
I have been asked to create a spreadsheet that has a front sheet where you can free type search criteria and then it will populate rows below with all search results from the remaining sheets within the workbook, for example;
In cell N15 I type the search criteria and hit a button
In cells F31-O31 it returns the following information;
CategorySub-CategoryCourseDrop Down Option 3SolutionFurther information or support
I have little to no experience in writing macros
I want to search say in column A for specific words say “Don” and then write in another column (say column 2) as “one”. Then it should keep searching for specific word until the last cell in that column and keep writing specific word in another column. It should do nothing if that specific word is not found.
I was able to use IF(ISERROR) but there is limitation of max 6 searches. Also i was able to achieve it by so many iteration of IF(ISERROR) and using autofilter.
My goal is to search around 30 to 40 criteria in one shot or by writing macros.
I guess i have presented my problem properly.
I'd like to create a search bar where someone types text into a field in Sheet1 (in the sample B1), then clicks a macro button to search for that word in Sheet2.
The result should be that the cell containing that word is where you end up.
The search word shouldn't be an exact match for the result, the cell just needs to contain it.
The search bar should also be present on Sheet2, with an additional button for Find Next, so that the user can move to the next result if the first one is not what they're looking for.
I've managed to create a search function with a pop up box using the below but this isnt as effective.
[Code] .....
Sample.xlsx‎
I need some help in creating a search box in excel. I have 4 rows of data, and want the user to enter a 4 digit number in a text box, which is then searched in the spreadsheet, pulling up the results.
So far I have the following code..
Sub Button1_Click()
'Opens box and ask what do they want to search
searchthis = InputBox("Type in a location keyword.", "Property Search")
'Tells where to search
Columns("A:E").Select
'and then search in them whatever the user entered:
Selection.Find(What:=searchthis, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext).Activate
End Sub
It only highlights the results, leaving all other data on the spreadsheet. Ideally id want to see only the results of the search. Is there anyway to do this?.
Also, if the search returns no results, I get a debugger message. I want some sort of error message to come up.
I have 2 workbookts: 1) Product; and 2) Buyers.
1) In the Products worksheet, every column has data about the books' description like:
A | B | C | D | E
Serial | Desc. | Author | Pubd. | year
Column A contains serial no. of the books, ordered from 1,2,3,4,5,6,7 to .....so on...
Columns B,C,D and E, have other details of the books.
2) In the Buyers worksheet, there are two columns;
A | B
Serial | Buyer Info
These are the details people who bought the particular book order by the serial no. of the book purchased.
Required
I need to make a new column in the first worksheet i.e. Product, say F, which I want to read all the buyers' info of that purchase the book with that serial no. and concatenate them together in the same cell separated by a comma.
I've got a code that stores a variable and then searches for that variable in a different file. When it finds the answer it it jumps over and up and highlights a different cell. The problem now is that I want it to keep searching past where the first result was and repeat this process down the entire column. Here's what I have so far:
Code:
Sub Find()
'
' Find Macro
' Finds a specific key word or phrase from the search box and codes the matches green.
MsgBox "This may take a moment, please be patient."
' This next line establishes the fact that the function will use a variable.
Dim FindClass As String
On Error GoTo ErrorCatch
[code]....
Adding a search engine like feature to a spreadsheet we have which has multifarious columns of data on it, here is a screenshot of what it looks like:
[URL]
We have multiple sheets for each tutor like that picture which lists all information of their learners for reference, now I want to do a search feature on the main home page or perhaps on a dedicated "Search" page which allows us to filter out specific information out of those columns, mainly the ability to search by "Tutor" (aka sheet name), "Postcode", "Venue", "End Date", "Learner Name" and "EBS" number...
Is it possible to make a hyperlink that will jump to a section that is not based on cells (A10, etc..) but rather on column (A) and a certain value in the cell, for instance, 2.1 or 2.2 or 2.3. The reason I want to do this is that when I have to add in new rows I do not want to have to redo all the hyperlinks since the cell shifted.
View 2 Replies View RelatedI'm currently trying to put together a search function of sort onto an excel document to look for entries of "Y" based on the choices in a drop down list to populate a list of names that have a "Y" next to them. I've searched the net and read up on IF, VLOOKUP and HLOOKUP commands but I can't quite string something together. An example of what I am trying to achieve is below;
Raw Data:
type1type2type3type4room1room2room3room4
AndyYNNNnYNY
BillYYNYYYYY
ChrisnYNYYYYY
DaveYNNNYYYY
EddyYNNNYYYY
FrankYYNYYNNN
GilesYYNYnYNY
HarveynYNNYNYN
IannYNYYYYY
JohnYYNYYYYN
KyleYYNYYYYY
Search drop downs:
Typetype1
Roomroom1
End result (based on the example of Type1 and Room1):
Bill
Dave
Eddy
Frank
John
Kyle
I am trying to create a uniform stock database for my company. Thus far, I have 2 worksheets. The first is a face sheet, for the user to interact with. This face sheet has a number of drop down menus on it allowing them to select "Type" and "Size" of the item they need. This information is then translated (by a hidden table) into an item code.
The second sheet contains a list of all the uniform items currently in stock. Each item has its individual code that correlates to the code the face sheet generates.
My question is regarding a search macro.
I want the user to be able to click a command button on the face sheet, labelled "search". This would then take the generated "Item Code" and search for it on the second worksheet. I am aware that it would be very easy for them to simply copy the code into "find" on the second sheet, however it needs to be as easy and quick to use as possible.
I'm only moderately skilled with Excel, and I work primarily with the logical, not mathematical functions. That being said, I'd like to know if there is a good method for Excel to solve this problem. I've been working at it in a rather sloppy manner until I began researching some other approaches.
The Problem(s):
I am developing a site that teaches the grammar of modern Chinese at the same time that it builds vocabulary by frequency. There are a couple of issues with this:
Chinese word corpi are virtually nonexistent, and where existent, sloppy.
Chinese character corpi are more available and somewhat more reliable.
The difference between a word and a character in Chinese can be a bit hazy, but the easiest way to put it is that not all characters stand alone as word and that most words are two to three characters in length.
Therefore, using data from the character corpus and a few thorough dictionaries, I created a word list in Excel that has the words and definitions in separate columns.
I have likewise found a character corpus that I have appropriately organized by ranking and other relevant data. The characters occupy a column.
Here's the basic logical function I am trying to create in separate columns, which are organized by frequency of the character:
=IF(AND(OR(contains any characters in the character corpus from frequency x to frequency y),NOT(OR(contains any characters in the character corpus from frequency y+1 to z)),Word Column,"")
Now, I know now that I have a good workaround, so I can shorten this to...
=IF(NOT(OR(contains any characters in the character corpus from frequency y+1 to z)),Word Column,"")
The problem is that I don't see any effective means of efficiently coding the OR condition!
Is there some magic that can be done so that I can use the ISNUMBER(SEARCH(characters,word cell) function for multiple characters?
I definitely don't want to go...
=IF(NOT(OR(ISNUMBER(SEARCH("‹…",word cell)),ISNUMBER(SEARCH("Ž®",same word cell),...),Word Column,"") for thousands upon thousands of characters!
Is there a way for me to select a range of values within the SEARCH() function to allow this kind of programming?
I also had a look at the filters, but found myself facing the same problem.
I've been trying to put together a VBA code that allows me to find a number between 1000 - 10,000. I am able to find any number below 999 but can seem to find anything above 1000. I've been using the following ....
View 13 Replies View RelatedI need to conduct keyword searches in excel and color the keywords a different color without changing the font color of the entire cell. now, i found some code from another thread that does this, but i cant seem to get it to work with more than 2 keywords. i need fine-tuning the provided code to do what i need it to do.
[URL]
the VBA code i used was this:
[Code].....
I was playing around with the example, the Cat_Mouse.xlsm, and when i modified the 'myList' and 'myColor' arrays, it does not work.
Cat_Mouse.xlsm
For example, I modified the code to add the word bat:
[Code] .....
When i run the macros, the word "Bat" does not become colored red. interestingly enough, when i substitute the word "bat" for the word "hat" in the "myLIST array, the word "hat" does change to the red font.
I am looking to use this code to address keywords in my excel file by coloring all key words red and i have more than 10 key words.
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
[URL] ......
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
I have question about next worksheet, let say I have VB code that’s Run on sheet1, but Sheet2 to Sheet 7 al so needs after sheet1 finished, same format on at sheets.
I need to protect the attached to stop people deleting the formulas. The problem is that there are hidden macros and when i protect the cells the conventional way they stop working and i lose all the data in the IAS sheet. I have highlighted all the cells in yellow that i want to be able to change once protected.
At this stage I would just like some one to explain how to protect it. Ideally I want to do it normally (unlock the cells individually that I want to remain unprotected, then protect the whole sheet). If its not then presumably its a macro alteration that's needed?? don't do it at this stage as depending on how it will be protected will mean that i need to make a few adjustments.
I've been reading about usage of worksheet functions in macros, and I guess I'm still not getting it. What I want to do, is use Countif and Indirect worksheet functions in a macro but it always fails. Here is my formula
IF(Worksheetfunction.Countif(Worksheetfunction.INDIRECT("[DaysWorking.xls]'Collect No Stats'!$A:$A"),'2009'!Range("D" & x))>0)
Basically, I want to see if 2 different cells in 2 different workbooks match and then I would use the information to write different info in another cell. X is just a variable in a DO WHILE LOOP. Can these functions be used in a macro? Is there an alternative?
It's simple enough to delete all macros. I have a form that starts as a landing area for a bunch of data then it gets renamed. I want to delete both of my command buttons from the sheet and remove all of my macros. How am I going to pull this off, how can i save it to the sheet the first time?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'cut command buttons
ActiveSheet.Shapes("CommandButton1").Select
Selection.Cut
ActiveSheet.Shapes("CommandButton2").Select
Selection.Cut
[Code] .........
I have a worksheet "OPEN", and would like to apply both:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim i As Long
Set rng = Target.Parent.Range("B:B")
If Target.Count > 1 Then Exit Sub
[Code] .........
The first removes the rows if the validated column 'B' is equal to "closed", to a second worksheet "CLOSED". The second orders the rows by the same column, followed by column 'A'. Being brand new to macros, I've no clue how to make this happen...
I have a website that I have been posting filtered worksheets of specific formats of items for interested viewers to view. The Looker can click on the Button and Open or Save a Copy of the Worksheet. I hide unused rows and columns to reduce size.
I filter my main worksheet to find the item type that I want to isolate - I copy the Result to another file - Run a Macro to Remove the information that I dont want to show. Hide Unused rows and Columns. Save as my File name and Upload to the Website.
BUT -- In my Excel Workbooks I have Macros - Some in the personal file. Some attached to Specific Files. When the Viewer clicks to see the file -- it offers the Macros ( Enable or Disable ). This Scares off many viewers and they Cancel.
When I delete the macros and save -- it deletes the Macros from MY computer as well.
How can I remove Macros from only the Sheet that I want to post for my website.
Here is the URL of one of the Current Buttons to a posted file if you care to view and see what I mean and am attempting to do.
I've managed to be able to create a macro to make a copy of a worksheet within excel.
However, when that copy is create all the macros are lost. If it is not possible to copy all the macros that are within the "Original Hours" sheet, I at least need a macro that will take people back to the original hours sheet (we have disabled sheet tabs, so need the macros to navigate through the worksheet).
Code used is below
Private Sub Hours_Click()
Sheets("Original hours").Copy After:=Sheets("Original hours")
ActiveSheet.Name = "Update hours"
ActiveSheet.Unprotect "PASSWORD"
With ActiveSheet.UsedRange
.Value = .Value
Sheets("Update hours").Select
End With
End Sub