How To Use A VBA Input Box To Create A Customized Excel Search Function
If you use Excel to store data, sometimes you need to search for information quickly and efficiently. For example if you take a call from an important customer you might need to access product or pricing details.
A few lines of VBA code can create an input box which you can use to search for information in a worksheet. A typical scenario might go something like this:
Answer an incoming call and a customer requests price informationOpen the "Prices" Excel fileAn input box requests the search textThe VBA code highlights any items found matching the queryCreating The VBA Search Code
We want the input box to activate as soon as the file opens so we place the code in the workbook section of the VBA code window, not as a general module.
private sub workbook_open()' the rest of the code goes here
end sub
The first thing we'll do is create an inputbox where a user can enter the search term. You can customize the input box with prompts and a title, but we'll stick to a standard format:
txt = InputBox("Search")Next, we'll activate the worksheet which holds the searchable data, clear any previous search results and define the range to search.
Sheets("search").ActivateCells.ClearFormats
Range("a1").CurrentRegion.Select
The code now has a search string to look for, and a data range to search through. We'll look for lower case versions of the data and the text so that a user doesn't have to worry about matching case.
For Each c In SelectionIf we find a match we want to be able to highlight the entry; in this scenario we'll highlight the cells from the first column to the last column of data rather than the entire row.
The advantage in using the instr function is that a partial match can be found, saving the user precious time. Typically, when searching for "widget" experience might tell the user the best approach is to simply type "widg" in the search box.
If InStr(LCase(c.Text), LCase(txt)) Thenfirst = Range(c.Address).End(xlToLeft).Address
last = Range(c.Address).End(xlToRight).Address
Range(first & ":" & last).Interior.ThemeColor = xlThemeColorDark2
End If
Next
Range("A1").Select
The code can be edited to search a specific column or worksheet and it would be possible to search all the sheets in a workbook with a little extra coding.
Summary
This code is an example of how some innovation can dramatically improve business processes, in this case finding information quickly and efficiently. And while Excel has many built-in functions to achieve the same result, a little knowledge of VBA combined with your own business knowledge can bring dramatic workplace improvements.
Andy L Gibson is a former Web Site programmer rediscovering his interest in software applications for small business.
Previously he has used AJAX, XML and ASPX to program Web Sites for restaurants, auction houses and Heath Authorities. He is keen to answer questions and explore business opportunities whereever they might present themselves. You can talk with Andy at his blog at http://solutions4business.wordpress.com/ including links to other articles on VBA issues he has written.
Beginner Steps For Developing iPhone Apps
Being a good programmer is something most youth dream of nowadays. This is evidenced by the rising number of students enrolling to study Information Technology at colleges. Many people have good idea for developing iPhone apps that could generate income for them in the future.
An aspiring developer should not worry if he does not have any experience developing applications. He could begin by analyzing the requirements, and making a decision on the kind of applications he wants to develop. A developer can then choose the programming and database language, and begin coding to launch his application.
Making errors, getting stuck, staying overnight, and feeling frustrated will be the norm during the first application development. However, after that one will be pleased to develop other applications since experience is the best way to learn. So, a budding developer should not be discouraged when his initial attempts at designing an application fails, it is a learning experience.
Before an individual starts doing programming related things, he ought to collect all requirements that must be in the system. If a person intends to make an easy program for his cakes site, he could begin by classifying the kinds of cakes to sell, the ingredients, prices, and employees' wages. It is suggested to list all things that are connected to program one is going to develop.
Now, an individual ought to have got some imagination of how the application will help his works. If a person needs to be able to access data from far places, he needs to develop a web-based application, so he could observe the transactions easily. On the other hand, if a person does not need to access it online, he could develop a desktop-based application that is easier and faster to build.
Thereafter, a developer ought to decide the database and programming language to be used. If a person has an extra budget, he could build the program using a non-open source database and programming language, such as VB.NET/ASP.NET and SQL Server. If a developer has a restricted budget, he should develop using PHP and MySQL for web-based application.
Lastly, the developer should design the tables and forms. This is the final phase he ought to follow prior to starting to code. To avoid forgetting to include the data required, he could design the forms at the same time so he could update the data whilst creating the forms. If a budding developer follows the steps involved in developing iPhone apps provided above, he is sure to come up with an app that will sell.
Visit Create-iphone-apps-now.com and discover how to start developing iPhone apps with no programming experience, and discover the secret to successfully market your iPhone app ideas in the app store.
How to Develop SEO-Friendly AJAX Website
The Ajax is a programming language which performs exchange of data with a server and updates web pages without having to reload the entire web page. It is a whole group of interconnected web development techniques used as client-side scripting language to create web applications. It receives data without intervening in the display of the existing pages and the behavior. Ajax is a group of technologies rather than a single technology such as HTML and CSS. The methods used to transfer or exchange data are XMLHttpRequest and JavaScript.
The Ajax development is providing a broad group of web technologies which in turn are used to implement web applications that interact with the server in the background without disturbing the present condition of the web pages. The Ajax website development is based on HTML and CSS technologies. XML is used for the Ajax web development for interchanging the data from server to the user. However from the latest developments in Ajax application, the use of the XML has been best-cited and so the use of XSLT is also not in use, although preferred formats are HTML or plain text which is enough user-friendly.
The Problem of Crawling Ajax
Ajax and SEO is more or less a very complicated topic. An Ajax site is more of a flash website, that is, with poor navigation. Since it mostly depends on JavaScript there arises big problem of crawling Ajax. Each browser exhibit different functions and features. In addition to this, running JavaScript needs additional resources which further elevates the costs for the search engines. Even though Google has made declarations that they are trying to understand better HTML forms, Flash and JavaScript, still it is not advisable to rely on these technologies as they are not Search Engine friendly.
Guidelines for developing SEO-friendly Ajax-website:
If you are developing a website or have a website, then surely you need a user-friendly Searching Engine which will attract the visitors and provide them hassle free content and searches. You should have a static HTML content which runs without interruptions and provide the users the needed information.
Use of ASP.NET
The use of the ASP.NET is very important while designing a user-friendly Ajax website, as it robust the server-side dictating the language which help the websites to provide smooth flow of the information.With the help of the ASP.NET the web technologies are put together for the creation of most influential websites and applications. Use of the HTML and JavaScript should be used form the side of the client to provide him user-friendly information and services.Creation of HTML page
The use of HTML is needed for the development of the Ajax websites, place all your files in the web server and save them in html extension, this will make the website to offer speedy information.On the second side add the JavaScript to send the request to the server which will enter certain codes. After a response you can save the pages which will be in server-side language.Use Ajax loader
While creating a page build a new ASPX page of your projects and name it as Ajax.aspx, this will be named as Ajax container page, it helps you to load the URL query string parameters. the Ajax loader will load the content to the pages more quickly and provide uninterrupted information.Determine the Ajax data
While creating a website it is important to put the Ajax application to your website. This makes the server request for the information faster and with one click the information is reveled before the user without wasting the time.Implementing the following simple practices may help you making your Ajax site crawlable:
Take note of the location of your website's end and where your application starts: It is meaningless to make robots crawl your drafts, but instead the VMWare Octopus documents made public and archived.Do confirm that there is a unique URL for each webpage that is to be indexed and crawled.Load the basic content having the keywords at the beginning in a static manner.Cache dynamic pages and make them appear as static pages. Do take snapshots of webpages whose content keeps on changing regularly and publish the snapshots.Links, menu items and other important structural aspects of a website should be independent of Ajax and/or JavaScript.Use Ajax for additional functionality that truly improves the user's experience. Do not use Ajax for effects.There are a number of ways which make the websites easy and user-friendly with the help of Ajax and the Ajax websites are running through-out the world with effectively. Almost all websites today run on Ajax which is embedded technology.
Hire Ajax developer & Ajax web services from TalentsfromIndia for effective and profiting Ajax development.
Subscribe to:
Posts (Atom)