Creating Form in excel. In order to create a proper excel data entry form, we should maintain accurate data in the table so that it will be easier for the end user to add, view and edit it. Let’s see in this example how to create a normal employee data entry form in excel which is shown below. Open the Excel applicationSelect the column with the date Choose Data on the top menu barText to ColumnsClick NextNextIn the third pane of the dialog, select the Date: radio button and choose DMY from the dropdownClick Finish. Learn the basics of using Microsoft Excel for Mac, including the anatomy of a spreadsheet, how to enter data, how to make your data look good so it's easier. By default, Function keys on a Mac control the computer itself, things like screen brightness, volume, video pause and play, and so on. This means that if press only the function keys in Excel, you'll end up controlling the Mac, and not Excel. To make function keys work like you expect in Excel, you need to add a key: the function or fn key.
Making and Using Excel web queries
Web queries bring tables from the web directly into Microsoft Excel. These instructions explain how to make and use web queries for Microsoft Excel on your Mac.
By Jim Gordon, co-author ofOffice 2011 for Mac All-in-One For Dummies
Data in Web pages
Data in web pages can be displayed a variety of ways:
- In row and column tables that are HTML based
- As the result of a query
- As a picture
- Within a PDF file
Getting the data
You can get HTML row and column data by running a web query. A web query is a text file saved with a .iqy file extension. A web query file contains the URL of the web page that holds the data.Free samples - but now they're broken!
Excel comes with these three free sample web queries that were supposed to let you see web queries in action: Dow Jones put the kabash on these samples, so they no longer work.
- MSN MoneyCentral Currencies
- MSN MoneyCentral Major Indices
- MSN MoneyCentral Stock Quotes
See below ffor how to make your own queries. Once you've made a web query, follow these instructions to run it.
Run a web query
Your Mac must have a live internet connection to run queries on web pages that are found on the internet.
There are three ways to run a web query in Excel 2011. Excel comes with some example web queries. To try one of the example queries, run the MSN MoneyCentral Currencies query using the first method described below. The result set will be a data range containing up to the minute currency exchange rates for the US dollar vs other currencies. The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.
- From the Data menu choose Get External Data > Run Saved query.
- The Choose a Query dialog displays defaulting to the Queries folder.
- Select a query file to run in the Choose a Query dialog.
- Click the Get Data button.
- The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
- Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.
- Click the Run Saved Query button. Because Microsoft forgot to put this button into the Ribbon, to use this option you must first add the Run Saved Query command to a toolbar Once you've added the Run Saved Query command to a toolbar:
- Click the Run Saved Query button.
- The Choose a Query dialog displays defaulting to the Queries folder.
- Select the query file to run in the Choose a Query dialog.
- Click the Get Data Button.
- The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
- Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.
- Run a query using a VBA (Visual Basic for Applications) macro
- Click here for instructions on how to display the Visual Basic Editor (VBE)
- Click here for an introduction to how to code using the VBE
- For an example, see heading below on this page: Web queries and Visual Basic for Applications (VBA)
To refresh the data from the web page, select any cell within the query results and then from the Data menu choose Refresh Data.
Web query results are displayed differently in Excel from the way the look on a web page. Formatting is minimally retained. Hyperlinks may or may not work. 'Relative' hyperlinks and specialty hyperlink protocols will not work when clicked.
Make a simple web query
The simplest web query consists of the URL of a web page which has an HTML table saved as a text file. To make your web query, take the following steps:
- Open Microsoft Word to a new, blank document
- Type or paste the URL of the web page that has an HTML table into your document. In this example use http://www.agentjim.com/. When pasting a URL, use the little widget that appears and Keep Text Only from the pop-up menu.
- Use File > Save As and choose file format Plain Text (.txt). Give the text file a sensible name. Navigate to this location: Applications:Microsoft Office 2011:Office:Queries and then click the Save button.
- The File Conversion dialog opens. In the options for Text Encoding choose MS-DOS, and in the Options section click the check box for Insert Line Breaks. Then click the OK button to complete saving your file.
- We're done with Word. If you want to he a purist, Take this optional step: in Finder, navigate to the file you just saved. Change the file extension from .txt to .iqy. You have to do this in Finder because Word won't let you change the file extension form .txt to .iqy when you save the file. The web query file will work even if you don't change the file extension to .iqy and leave it as .txt.
Now you can run your saved web query as described earlier.
Make a dynamic (parameter) web query
Some web pages request input so that custom result sets can be delivered. You can see how such a web query is constructed by opening the files in Microsoft Word to examine the contents of the built-in web queries. When you run the web query MSN MoneyCentral Stock Quotes you are prompted to enter the code for a particular stock. If you use Microsoft Word to open the sample web query MSN MoneyCentral Stock Quotes you can see the text for that query. This query uses the formal structure for a web query. The question mark embedded after the URL triggers Excel to display a dialog box prompt for the web page, which is expecting a stock market SYMBOL parameter from a web browser. QUOTE is the name of the parameter (See Figure 3 below):
WEB
1
http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=['QUOTE','Enterstock, fund or other MSN MoneyCentral Investor symbols separated by commas.']
Selection=EntirePage
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
Here's another example of a dynamic web query. This simple is for Yahoo Finance where jasz is a stock market symbol:
http://www.finance.yahoo.com/q?f=jasx
This example also queries Yahoo, but this one puts the results into a single column
http://finance.yahoo.com/d/quotes.csv?s=ORCL&s=CSCO&s=COHR&f=sl1 Working with query result sets
The result set querytable of a web query has many properties you can control. When you first make your web query, there are settings available to you in the Returning External Data to Excel dialog (see Figure 1).
Figure 1 - Returning External Data to Excel
Click the Properties button to display the External Data Range Properties dialog when you first make a query. You can also display this dialog by right-clicking into the querytable result set and choosing Data Range Properties from the pop-up menu. The options presented are context sensitive. Not all options are available for web queries.
Figure 2 - External Data Range Properties
If your web query is dynamic (requests a parameter), the Parameters button becomes available in the Return External Data to Excel dialog box. Click the Parameters button to display the Parameters dialog, (see Figure 2). In this dialog you can modify the text of the prompt, enter a value to always use, or choose a cell and use that cell's value to satisfy the prompt. The Parameters dialog can also be displayed by right-clicking in the querytable result set of a parameter query and choosing Parameters from the pop-up menu.
Note the option to 'Fill down formulas in columns adjacent to data.' Enabling this option lets you use calculated columns with the web query result set.
Figure 3 - Parameters
Web queries and Visual Basic for Applications (VBA)
You must use full URLs and they must be http:// type URLs. Here is a code example showing VBA syntax:
Sub MakeWebQuery()
With ActiveSheet.QueryTables.Add(Connection:= _
'URL;http://www.domainname.com', Destination:= _
Range('A1'))
.PostText = 'local'
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 1
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
.UseListObject = False
End With
End Sub
With ActiveSheet.QueryTables.Add(Connection:= _
'URL;http://www.domainname.com', Destination:= _
Range('A1'))
.PostText = 'local'
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 1
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
.UseListObject = False
End With
End Sub
Use the following code to refresh a query. The selection cursor must be in the QueryTable before running this code. If a parameter query is refreshed, the user will be prompted for parameters.
Sub RefreshQuery()
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
This Excel tutorial explains how to use the Excel FOR..NEXT statement to create a FOR loop in VBA with syntax and examples.
Description
The Microsoft Excel FOR..NEXT statement is used to create a FOR loop so that you can execute VBA code a fixed number of times.
The FOR..NEXT statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.
If you want to follow along with this tutorial, download the example spreadsheet.
Syntax
The syntax to create a FOR Loop using the FOR..NEXT statement in Microsoft Excel is:
Parameters or Arguments
- counter
- The loop counter variable.
- start
- The starting value for counter.
- end
- The ending value for counter.
- increment
- Optional. The value that counter is incremented each pass through the loop. It can be a positive or negative number. If not specified, it will default to an increment of 1 so that each pass through the loop increases counter by 1.
- statements
- The statements of code to execute each pass through the loop.
Returns
The FOR..NEXT statement creates a FOR loop in VBA.
Note
- See also the WHILE..WEND statement to create a WHILE loop in VBA.
Applies To
- Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Example (as VBA Function)
The FOR..NEXT statement can only be used in VBA code in Microsoft Excel.
Let's look at how to create a FOR loop in Microsoft Excel, starting with a single loop, double loop, and triple loop, and then exploring how to change the value used to increment the counter each pass through the loop.
Single Loop
The simplest implementation of the FOR loop is to use the FOR..NEXT statement to create a single loop. This will allow you to repeat VBA code a fixed number of times.
Skype command line switches for macos update. For example:
In this example, the FOR loop is controlled by the LCounter variable. It would loop 5 times, starting at 1 and ending at 5. Each time within the loop, it would display a message box with the value of the LCounter variable. This code would display 5 message boxes with the following values: 1, 2, 3, 4, and 5.
Text To Columns In Excel Mac
Single Loop - Changing Increment
By default, the FOR loop will increment its loop counter by 1, but this can be customized. You can use
STEP increment
to change the value used to increment the counter. The FOR loop can be increment can be either positive or negative values.Positive Increment
Let's first look at an example of how to increment the counter of a FOR loop by a positive value.
For example:
In this example, we've used
Step 2
in the FOR loop to change the increment to 2. What this means is that the FOR loop would start at 1, increment by 2, and end at 9. The code would display 5 message boxes with the following values: 1, 3, 5, 7, and 9.Negative Increment
Now, let's look at how to increment the counter of a FOR loop by a negative value.
For example:
When you increment by a negative value, you need the starting number to be the higher value and the ending number to be the lower value, since the FOR loop will be counting down. So in this example, the FOR loop will start at 50, increment by -5, and end at 30. The code would display 5 message boxes with the following values: 50, 45, 40, 35, and 30.
Double Loop
Next, let's look at an example of how to create a double FOR loop in Microsoft Excel.
Mac Text Editor
For example:
Here we have 2 FOR loops. The outer FOR loop is controlled by the LCounter1 variable. The inner FOR loop is controlled by the LCounter2 variable.
In this example, the outer FOR loop would loop 4 times (starting at 1 and ending at 4) and the inner FOR loop would loop 2 times (starting at 8 and ending at 9). Within the inner loop, the code would display a message box each time with the value of the LCounter1-LCounter2. So in this example, 8 message boxes would be displayed with the following values: 1-8, 1-9, 2-8, 2-9, 3-8, 3-9, 4-8, and 4-9.
Triple Loop
Next, let's look at an example of how to create a triple FOR loop in Microsoft Excel.
For example:
Here we have 3 FOR loops. The outer-most FOR loop is controlled by the LCounter1 variable. The next FOR loop is controlled by the LCounter2 variable. The inner-most FOR loop is controlled by the LCounter3 variable.
In this example, the outer-most FOR loop would loop 2 times (starting at 1 and ending at 2) , the next FOR loop would loop 2 times (starting at 5 and ending at 6), and the inner-most FOR loop would loop 2 times (starting at 7 and ending at 8).
Within the inner-most loop, the code would display a message box each time with the value of the LCounter1-LCounter2-LCounter3. This code would display 8 message boxes with the following values: 1-5-7, 1-5-8, 1-6-7, 1-6-8, 2-5-7, 2-5-8, 2-6-7, and 2-6-8.
Example#1 from Video
In the first video example, we are going to use the For..Next statement to loop through the products in column A and update the appropriate application type in column B.
Example#2 from Video
In the second video example, we have a list of participants in column A and we'll use two FOR Loops to assign each of the participants to either Team A or Team B (alternating between the two).