Use For ... Next loop if the number of loops is already defined and known. A For ... Next loop uses a counter variable that increases or decreases in value during each iteration of the loop. This loop structure is being used the most for our examples on this site.
Here is an example of the For ... Next loop: For i = 1 to 10 Cells(i, 1) = i Next i In this example, i is the counter variable from 1 to 10. The looping process will send value to the first column of the active sheet and print i (which is 1 to 10) to row 1 to 10 of that column.
Note that the counter variable, by default, increases by an increment of 1 For ... Next Loop With Step
You can use the Step Keyword to sepcify a different increment for the counter variable.
For example: For i = 1 to 10 Step 2 Cells(i, 1) = i Next i This looping process will print values with an increment of 2 on row 1, 3, 5, 7 and 9 on column one.
You can also have decrement in the loop by assign a negative value afte the Step keyw…
The If ... Then ... Else statement is used to define two blocks of conditions - true and false.
If Age >=22 Then Drink = "Yes" Else Drink = "No" End If Note that End If statement is needed in this case as well since there is more than one block of statements The IF ... Then ... ElseIf is used to test additional conditions without using new If ... Then statements.
If Age >= 18 and Age < 22 Then Msgbox "You can vote" ElseIf Age >=22 and Age < 62 Then Msgbox "You can drink and vote" ElseIf Age >=62 Then Msgbox "You are eligible to apply for Social Security Benefit" Else Msgbox "You cannot drink or vote" End If Note that the last condition under Else is, implicitly, Age < 18
The IF ... Then is a single condition and run a single statement or a block of statement.
Example, the following statement set variable Status to "Adult" if the statement is true: If Age >= 18 Then Status = "Adult" You can also use multiple-line block in the If statement as followed: If Ago >= 18 Then Status = "Adult" Vote = "Yes" End If
PressTo Do ThisPressTo Do ThisAlt+F11Open the VBA editorF1Get help on any selected itemAlt+F8Display a list of macrosF2Display the Object Browser windowCtrl+BreakPerform an emergency stop of the programF4Display the Properties windowCtrl+GDisplay the Immediate windowF5Start the programCtrl+IList the quick information for the selected elementF7Display the Code window after selecting a form or controlCtrl+JList the properties and methods for an objectF9Add a breakpointCtrl+RDisplay Project ExplorerShift+F7Display the form that corresponds to the active Code windowCtrl+Shift+IList the parameter information for the selected elementShift+F9Add a quick watch for the highlighted textCtrl+Shift+JList the constants associated with an enumerationCtrl+TabMove to the next Code or UserForm window
FunctionWhat It Does
Returns a number's absolute value
Returns a variant containing an array
Converts the first character of a string to its ASCII value
Returns the arctangent of a number
Returns a value from a list of items
Converts an ANSI value to a string
Returns a number's cosine
Returns the current path
Returns the current system date
Returns a date to which a specified time interval has been
added — for example, one month from a particular date
Returns an integer showing the number of specified time intervals between two dates, for example the number of months between now and your birthday
Returns an integer containing the specified part of a given
date — for example, a date's day of the year
Converts a date to a serial number
Converts a string to a date
In Excel, the ADDRESS function returns a text representation of a cell address.The syntax for the ADDRESS function is:ADDRESS( row, column, [ref_type], [ref_style], [sheet_name] )row is the row number to use in the cell address.column is the column number to use in the cell address.ref_type is optional. It is the type of reference to use. It can be any of the following values:ValueExplanation1Absolute referencing. For example: $A$12Absolute row; relative column. For example: $A13Relative row; absolute column. For example: A$14Relative referencing. For example: A1If this parameter is omitted, the ADDRESS function assumes that the ref_type is set to 1.ref_style is optional. It is the reference style to use: either A1 or R1C1. It can be any of the following values:ValueExplanationTRUEA1 style referencingFALSER1C1 style referencingIf this parameter is omitted, the ADDRESS function assumes that the ref_style is set to TRUE.sheet_name is optional. It is the name of the sheet to use in the cell …
Learning how to customise a cell format in Excel allows you to not only format your data the way you want, but in some instances it can save you time.Before we dive in you need to know that despite how the text appears after you’ve set your custom cell format, the underlying value is unchanged for the purpose of formulas and calculations.How to enter a custom cell formatSelect the cell/s you want to format then open the Format Cells window.The quick way just press CTRL+1Or the way most people do it is to right click and select ‘Format Cells’.On the Number Tab select Custom from the Category list.Note: It’s handy to have the text you want to format in the cell before you press CTRL+1 because Excel will give you a sample view of what the text is going to look like in the Format Cells window, so you can see before pressing OK, if it’s what you want.How to make your cell formats look the way you wantCustom Cell FormatsText Before FormattingCustom FormatFormatted TextBrackets for negative …