If Statement Last updated: 2021-03-04

Visual Basic logical statement. Below are some logical statement in VBA

1.1. If Else

VBA If Statements allow you to test if expressions are TRUE or FALSE, running different code based on the results. Let’s look at a simple example.

Note

There three condition and statement. The third condition in <else> don't need to be called in the syntax.

1.2. If Loop

Loop

Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.

2.1 For Next

2.2 For Each

2.3 Do While

2.4 Select Case

Lookup

LOOKUP Function in Excel VBA. The LOOKUP function in Excel is used to search a value in an array and return its corresponding value from another column.

3.1 Vlookup

3.2 Index Match

Useful Stuff

These VBA codes will help you to perform some basic tasks in a flash which you frequently do in your spreadsheets.

4.1 Paste Value

4.2 Autofilter

4.3 Range End

4.4 Return Row / Column

4.5 Move One Row

Structuring Your Code

It’s not uncommon to get halfway through writing a code project before realizing that we should’ve structured it differently or created a separate function for specific elements of our code. Planning the structure of our VBA, what you want it to achieve, what cell we’ll need etc. will save you time in the long run. It also gives us time to think about potential errors that may arise along the way and how we plan to deal with them. We have Two step here :

Define The Table (Manually).
Define The Variables (Header Range).

5.1 Define The Table

Define Table Earliest and Latest Cell (UpperLeft and LowerRight), Then retrieve the properties. Example Range (B2:F7)

Now we have four new reference here

Table 1 Variable First T1V1.
Table 1 Variable Last T1VL.
Table 1 Observation First T1O1.
Table 1 Observation Last T1OL.

5.2 Define The Variables

Define The Variables to get its Range (Header). We'll use it to find variable based on its name (values). Example (B2:F2)

Now we have A new reference here

Header in Table 1 H1.

5.3 Example

How to select all cell (values) from a single variable (column). example Range(C3:C7) ?

Search Variable's Cell in Header H1, then return the cell (row and column) number.

Retrieve our target properties, Range C3:C7.

Get C3:C7 formula

Create a code !

>>

REFERENCES :

Vba articles. (2018, March 21). Retrieved April 21, 2018, from https://excelmacromastery.com/vba-articles/.
Rynearson, S. (n.d.). Learn vba online - tutorial for beginners (free and interactive). Retrieved April 06, 2018, from https://www.automateexcel.com/vba/.
Excel VBA loops - for each, for Next, do While, NESTED & more. (2020, November 06). Retrieved November 06, 2020, from https://www.automateexcel.com/vba/loops/
Loop in excel vba. (n.d.). Retrieved November 06, 2018, from https://www.excel-easy.com/vba/loop.html
MrExcel. (n.d.). Retrieved April 06, 2018, from https://www.mrexcel.com/
VBA tutorial. (n.d.). Retrieved November 06, 2021, from https://www.tutorialspoint.com/vba/
Excel vba basics. (n.d.). Retrieved November 06, 2018, from https://www.exceltrick.com/category/excel-vba-basics/
VBA index match: How to use Index match function in VBA (EXAMPLES). (2021, February 20). Retrieved March 06, 2021, from https://www.wallstreetmojo.com/vba-index-match/
Online Excel tips & tutorials. (2020, November 22). Retrieved November 22, 2020, from https://trumpexcel.com/