Trending November 2023 # How To Use Resize Property In Excel Vba Programming? # Suggested December 2023 # Top 20 Popular

You are reading the article How To Use Resize Property In Excel Vba Programming? updated in November 2023 on the website Tai-facebook.edu.vn. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested December 2023 How To Use Resize Property In Excel Vba Programming?

Excel VBA Resize

VBA Resize is a method where we use it to highlight the cells by changing their size for visualization. The important thing which needs to be noted is that this method is used only for illustration purposes. Resize is a property that is used with the range property method to display the selection of rows and columns provided as an argument concerning a given range.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of VBA Resize:

The syntax for this property is as follows:

Row size is the number of rows we want to select or highlight, and column size is the number of columns we want to select and highlight. This property needs a reference to a range. If one of the arguments from row size or column size is not provided, this function selects the entire row or the column from the range. The methods to use Resize property in VBA are as follows:

First, we need to provide a range for reference.

The next step is to provide the arguments for the rows and size for resize property.

To illustrate it, we need to use the select property method.

Using the Resize Property in Excel VBA

The following examples will teach us how to use Resize Property in Excel using the VBA Code.

You can download this VBA Resize Excel Template here – VBA Resize Excel Template

Example #1

Let us first begin with the essential resize property. In this example, we will see how to use resize the property and how we need to provide the inputs for this resize function in general. For this, follow the below steps:

Step 2: Now write the sub-procedure for VBA Resize.

Code:

Sub

Example1()

End Sub

Step 3: Resize is a range property of VBA; let us select a range.

Code:

Sub

Example1() Range("A1")

End Sub

Step 4: After that, we can use the dot operator and resize method to select the number of columns and rows.

Code:

Sub

Example1() Range("A1").Resize(RowSize:=2, ColumnSize:=2)

End Sub

Step 5: We can use the select method property for illustration purposes.

Code:

Sub

Example1() Range("A1").Resize(RowSize:=2, ColumnSize:=2).Select

End Sub

Step 6: Run the Code by hitting F5 or the Run button and see the result in worksheet 1.

We have selected two rows and two columns for the range.

Example #2

In the above example, we have used the same number of rows and columns for the range. Let us try a different approach and use diverse selection such as row size to be 3 and column size to be 2. For this, follow the below steps:

Step 1: We can use the same Module and begin with our sub-procedure for the second example.

Code:

Sub

Example2()

End Sub

Step 2: As this is a range property, we will use the range method to reference a cell.

Code:

Sub

Example2() Range("A1:C4")

End Sub

Step 3: Then, we will use the resize method and select the row and column size for the arguments.

Code:

Sub

Example2() Range("A1:C4").Resize(3, 2)

End Sub

Step 4: The final step is to use the Select property method for the illustration.

Code:

Sub

Example2() Range("A1:C4").Resize(3, 2).Select

End Sub

Step 5: When we execute the above code by hitting F5 we can see the following result in sheet 1.

Out of the range A1:C4, this code has selected three rows and two columns.

Example #3

So in the above examples, we saw how to resize property works if there is the same number of rows and columns or a different number of rows and columns as the argument. Now let us see what happens when we do not provide one of the arguments to the function. For this, follow the below steps:

Step 1: Declare another Subprocedure.

Code:

Sub

Example3()

End Sub

Step 2: Now, we can select any random range.

Code:

Sub

Example3() Range("A1:C4")

End Sub

Step 3: Now, we will use the resize property, but we will remove the row specification from the code.

Code:

Sub

Example3() Range("A1:C4").Resize(, 1)

End Sub

Step 4: Now, we will use the select method.

Code:

Sub

Example3() Range("A1:C4").Resize(, 1).Select

End Sub

Step 5: Run the Code by hitting F5 or the Run button.

It selected one column but the entire four rows.

Example #4

Step 1: Let us begin with a sub-procedure in the same Module.

Code:

Sub

Example4()

End Sub

Step 2: First, we activate sheet 1 using the worksheet property method.

Code:

Sub

Example4() Worksheets("Sheet1").Activate

End Sub

Step 3: Now, let us provide the selection with the number of rows and a number of columns using the selection property method, as shown below.

Code:

Sub

Example4() Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count

End Sub

Step 4: Now, we can use the resize property to increase the selection by two rows and two columns.

Code:

Sub

Example4() Worksheets("Sheet1").Activate numRows = Selection.Rows.Count numColumns = Selection.Columns.Count Selection.Resize(numRows + 2, numColumns + 2).Select

End Sub

Step 5: So here is our selection before the execution of the code.

Code:

Step 6: When we execute the code.

The following code extended the selection by two rows and two columns.

Things to Remember

There are a few things that we need to remember about VBA Resize:

This is a range property method.

It illustrates the selection of rows and columns from a given range.

The first argument in this function is a row reference, and the second argument is a range reference.

If one of the arguments from row size or column size is not provided, this function selects the entire row or the column from the range.

Recommended Articles

This is a guide to the VBA Resize. Here we discuss how to use the Resize property in Excel VBA, practical examples, and downloadable Excel template. You can also go through our other suggested articles –

You're reading How To Use Resize Property In Excel Vba Programming?

How To Use Excel Vba Split Function?

VBA Split Function

As the name suggests, a Split is a function that splits strings into different parts. We have many such functions in excel worksheets, such as a left-right and mid function to do so. But when we need any string to differentiate in parts, we use a Split function in VBA. It is one of the best functions in VBA to perform different types of operations on strings.

The split function is basically a substring function that takes a string as an input and gives another string as an output. The only difference between the other substring function like left, right, and mid and split function is that the LEFT, RIGHT & MID function just take one string as an input or argument and returns one string as an output while the SPLIT function returns an array of strings as output.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Formula for Split Function in Excel VBA

VBA Split function has the following syntax:

Given below are the arguments for the VBA split function first:

Expression as String: This is a mandatory argument in VBA Split function. Expression as string refers to the string we want to break into parts.

Delimiter: This is an optional argument. It is the character that is used to break strings into parts. But if we do not provide any delimiter, VBA treats space “ “ as default delimiter.

Limit: This is also an optional argument. Limit means the maximum number of parts we want to do of a string. But again, if we do not provide a limit to the function, VBA treats it as default -1, which means the string will break apart each time there is a delimiter in the string.

Compare: This final argument is also an optional argument. Compare is a method that is described as one of the two below:

Either it is 0, which means Split will perform a binary comparison which means every character should match itself.

Or it can be 1, which means the Split function will do a textual comparison.

Everything will be clear in a few examples. But let me give a very basic example first of what this function does. Suppose we have an input string as ANAND IS A GOOD BOY. The split string will break it into parts, each word separately. We can also use the Split function to count a number of words in a string, or we can use it to output only a certain amount of words in a given string.

How to Use Excel VBA Split Function?

We will see how to use a VBA Split Excel function with few examples:

You can download this VBA Split Excel Template here – VBA Split Excel Template

VBA Split Function – Example #1

How about we use the above string ANAND IS A GOOD BOY with split function.

Note: In order to use a Split function in VBA, make sure that the developer option is turned on from File Tab from the options section.

Step 3: When the code window appears, declare a sub-function to start writing the code.

Code:

Sub

Sample()

End Sub

Step 4: Declare two variables arrays and one as strings A & B.

Code:

Sub

Sample()

Dim

A

As String

Dim

B()

As String

End Sub

Step 5: Store the value of the string in A.

Code:

Sub

Sample()

Dim

A

As String

Dim

B()

As String

A = "ANAND IS A GOOD BOY"

End Sub

Step 6: In the B array, store the value of A using the split function as shown below.

Code:

Sub

Sample()

Dim

A

As String

Dim

B()

As String

A = "ANAND IS A GOOD BOY" B = Split(A)

End Sub

Step 7: Use For Loop to break every string.

Code:

Sub

Sample()

Dim

A

As String

Dim

B()

As String

A = "ANAND IS A GOOD BOY" B = Split(A)

For

i =

LBound

(B)

To UBound

(B) strg = strg & vbNewLine & "String Number " & i & " - " & B(i)

Next

i

End Sub

Step 8: Display it using the Msgbox function.

Code:

Sub

Sample()

Dim

A

As String

Dim

B()

As String

A = "ANAND IS A GOOD BOY" B = Split(A)

For

i =

LBound

(B)

To UBound

(B) strg = strg & vbNewLine & "String Number " & i & " - " & B(i)

Next

i MsgBox strg

End Sub

Step 9: Run the code from the run button provided below.

We get this as output once we run the above code.

VBA Split Function – Example #2

We will now try to take input from a user and split the string into parts.

Step 3: In the code window, declare a sub-function to start writing the code.

Code:

Sub

Sample1()

End Sub

Step 4: Declare two variables, one as String and one as an Array String.

Code:

Sub

Sample1()

Dim

A

As String

Dim

B()

As String

End Sub

Step 5: Take the value from the user and store it in the A using the Inputbox function.

Code:

Sub

Sample1()

Dim

A

As String

Dim

B()

As String

A = InputBox("Enter a String", "Should Have Spaces")

End Sub

Step 6: Store the value of A in Array B using the Split Function.

Code:

Sub

Sample1()

Dim

A

As String

Dim

B()

As String

A = InputBox("Enter a String", "Should Have Spaces") B = Split(A)

End Sub

Step 7: Use For Loop to break every string.

Code:

Sub

Sample1()

Dim

A

As String

Dim

B()

As String

A = InputBox("Enter a String", "Should Have Spaces") B = Split(A)

For

i =

LBound

(B)

To UBound

Next

i

End Sub

Step 8: Display it using the Msgbox function.

Code:

Sub

Sample1()

Dim

A

As String

Dim

B()

As String

A = InputBox("Enter a String", "Should Have Spaces") B = Split(A)

For

i =

LBound

(B)

To UBound

(B) strg = strg & vbNewLine & "String Number " & i & " - " & B(i)

Next

i MsgBox strg

End Sub

Step 9: Run the code from the run button. Once we run the code, we get an input message to write a string. Write “I AM A GOOD BOY” as input in the input box and press ok to see the result.

VBA Split Function – Example #3

We can also use the VBA Split Function to count the number of words in the string. Let us take input from the user and count the number of words in it.

Step 3: Once the code window is open, declare a sub-function to start writing the code.

Code:

Sub

Sample2()

End Sub

Step 4: Declare two variables, one as a string and one as an array string.

Code:

Sub

Sample2()

Dim

A

As String

Dim

B()

As String

End Sub

Step 5: Take input from the user and store it in A using the input box function.

Code:

Sub

Sample2()

Dim

A

As String

Dim

B()

As String

A = InputBox("Enter a String", "Should Have Spaces")

End Sub

Step 6: Use the Split function and store it in B.

Code:

Sub

Sample2()

Dim

A

As String

Dim

B()

As String

A = InputBox("Enter a String", "Should Have Spaces") B = Split(A)

End Sub

Step 7: Use a Msgbox function to display the total number of words.

Code:

Sub

Sample2()

Dim

A

As String

Dim

B()

As String

A = InputBox("Enter a String", "Should Have Spaces") B = Split(A) MsgBox ("Total Words You have entered is : " &

UBound

(B()) + 1)

End Sub

Step 8: Run the code from the run button provided. Once we have run the code, it asks for an input for the string. Write “INDIA IS MY COUNTRY” in the box and press ok to see the result.

Explanation of Excel VBA Split Function

Now we know that the split function in VBA is a substring function that is used to split strings into different parts. The input we take is as a string, while the output displayed is an array.

It is very similar to the other worksheet function, but it is superior as it can break multiple words and return them as an array.

Things to Remember

There are a few things we need to remember about VBA split function:

The VBA split function is a substring function.

It returns the output as a string.

Only the expression is the mandatory argument, while the rest of the arguments are optional.

Recommended Articles

This has been a guide to VBA Split Function. Here we discussed how to use Excel VBA Split Function along with practical examples and a downloadable excel template. You can also go through our other suggested articles to learn more –

How To Use Excel Vba Instrrev With Examples?

VBA InStrRev Function

Knowing the occurrence of a string in another string can be very handy while working with day to day data. Obviously, we can do it manually by calculating the occurrence of the string in another string but that would the task very hefty. So to make it easier we have a function in VBA which is known as INSTRREV which is used to find the occurrence.

As explained above, INSTRREV in Excel VBA is used to find an occurrence of a string in another string. This function finds the first occurrence of a string in the target string and returns the value. Now we have to remember that as it gives the occurrence of the string so the returned value is numeric. Also as it is a comparison function so like other functions in VBA there are three basic comparisons methods.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of InStrRev in Excel VBA

The syntax for VBA InStrRev function in excel is as follows:

Now let us break down the syntax and learn about it, String is the main string from where we want to find the occurrence of a substring, Start is the numeric occurrence we provide to the string. If no start parameter is provided the function starts looking a string from the end of it. And compare is the comparison method we provide to the function. There are three types of comparison for this function:

To use Option Compare which is (-1). It is also known as VbUseCompareOption.

To use Binary Compare which is (0). It is also known as VbBinaryCompare.

To use Text Compare which is (1). It is also known as VbTextCompare.

Again if none of the compare options is provided then the function automatically considers it as a binary compare.

Now let us use this function in a few examples and look at how to use this function.

How to Use Excel VBA InStrRev?

Now let us try with some examples on VBA InStrRev in Excel.

You can download this VBA InStrRev Excel Template here – VBA InStrRev Excel Template

Example #1 – VBA InStrRev

Step 2: Once we enter the VB editor we can see in the header section, there is an option of insert. Insert a new module from that option as shown below.

Step 3: Now let us start our subprocedure in the module as shown below.

Code:

Sub

Sample()

End Sub

Step 4: Now declare a variable as an integer which will hold the output value of the function for us.

Code:

Sub

Sample()

Dim

A

As Integer

End Sub

Step 5: Now in the variable use the INSTRREV function to find the occurrence of “ “ in the string “ I am a Good Boy” as follows.

Code:

Sub

Sample()

Dim

A

As Integer

A = InStrRev(" I am a Good Boy", " ")

End Sub

Step 6: Now display the value stored in variable A using the msgbox function.

Code:

Sub

Sample()

Dim

A

As Integer

A = InStrRev(" I am a Good Boy", " ") MsgBox A

End Sub

Step 7: Let us execute the above code to get the following result.

We get the result as 13 because we did not provide the start position to the function so it automatically calculated the occurrence from the end and so the result. It is found that “ “ is on the 13th position of the string when we search it from the end.

Example #2 – VBA InStrRev

In the above example, we did not provide any start position to the string. Let us provide this time in this example. Let us find out from the second position where does the “ “ occurs in the string.

Step 1: Insert a new module from that option as shown below.

Step 2: Let us again define a subprocedure for our second example.

Sub

Sample1()

End Sub

Step 3: Declare another integer variable for the example.

Code:

Sub

Sample1()

Dim

A

As Integer

End Sub

Step 4: Now in Variable A let us find the occurrence of the “ “ from the second position using the INSTRREV function as follows.

Code:

Sub

Sample1()

Dim

A

As Integer

A = InStrRev(" I am a Good Boy", " ", 2)

End Sub

Step 5: Now use msgbox function to display the value stored in A.

Code:

Sub

Sample1()

Dim

A

As Integer

A = InStrRev(" I am a Good Boy", " ", 2) MsgBox A

End Sub

Step 6: Now run the above code to find out the below result as shown below,

We get 1 as a result as we count 2 we get I and after one position we get the occurrence of “ “.

Example #3 – VBA InStrRev

In this example let us use the compare methods. We have a string “ India is the Best” and let us find the string “E” using both text and binary compare methods.

Step 1: In the same module 1, write another subprocedure for example 3.

Code:

Sub

Sample2()

End Sub

Code:

Sub

Sample2()

Dim

A, B

As Integer

End Sub

Step 3: In variable A let us use the INSTRREV function with the text comparison as follows.

Code:

Sub

Sample2()

Dim

A, B

As Integer

A = InStrRev("India is the Best", "E", , vbTextCompare)

End Sub

Step 4: Now display the value stored in A using the msgbox function.

Code:

Sub

Sample2()

Dim

A, B

As Integer

A = InStrRev("India is the Best", "E", , vbTextCompare) MsgBox A

End Sub

Step 5: In variable B let’s use the binary comparison for the same string as follows.

Code:

Sub

Sample2()

Dim

A, B

As Integer

A = InStrRev("India is the Best", "E", , vbTextCompare) MsgBox A B = InStrRev("India is the Best", "E", , vbBinaryCompare) MsgBox B

End Sub

Step 6: Execute the above code to find the first result stored in variable A which is as follows.

Step 7: Press OK to see the result stored in variable B.

We get 0 as the result for binary compare because in our string “e” is present not “E”. In binary values both of these are different. So if a value is not found in the string we get a result as 0.

Things to Remember

The value returned by this function is numeric.

If the substring is not found the value returned is 0.

Start position is optional. If it is not provided, by default function search the occurrence from the end of the string.

The comparison methods are also optional.

Recommended Articles

This is a guide to VBA InStrRev. Here we discuss how to use Excel VBA InStrRev along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Create A Vba Macro Or Script In Excel

Microsoft Excel enables users to automate features and commands using macros and Visual Basic for Applications (VBA) scripting. VBA is the programming language Excel uses to create macros. It will also execute automated commands based on specific conditions.

Macros are a series of pre-recorded commands. They run automatically when a specific command is given. If you have tasks in Microsoft Excel that you repeatedly do, such as accounting, project management, or payroll, automating these processes can save a lot of time.

Table of Contents

In this article, we will explain the following:

Enabling Scripts & Macros

How to Create a Macro in Excel

Specific Example of a Macro

Learn More About VBA

Create a Button to Get Started with VBA

Add Code to Give the Button Functionality

Did it Work?

Enabling Scripts & Macros

Before you can create macros or VBA scripts in Excel, you must enable the Developer tab on the Ribbon menu. The Developer tab is not enabled by default. To enable it:

Put a tick in the box next to Developer.

Make sure the document is from a trusted source if you are working on a shared project in Excel and other Microsoft programs.

When you are done using your scripts and macros, disable all macros to prevent potentially malicious code from infecting other documents.

Create a Macro in Excel

All the actions you take in Excel while recording a macro are added to it. 

Enter a Macro name, a Shortcut key, and a Description. Macro names must begin with a letter and can’t have any spaces. The shortcut key must be a letter.

Decide where you want to store the macro from the following options:

Personal Macro Workbook: This will create a hidden Excel document with stored macros to be used with any Excel documents.

New Workbook: Will create a new Excel document to store the created macros.

This Workbook: This will only be applied to the document you are currently editing.

Specific Example Of a Macro

You can manually change this. Or you can create a program using a macro to automatically format it correctly for you.

Record The Macro

This will highlight the cells that have a balance due. We added a few customers with no balance due to further illustrate the formatting.  

Apply The Macro

When you run a macro, all the formatting is done for you. This macro we just created is stored in the Visual Basic Editor.

Users can run macros in several different ways. Read Run a macro to learn more.  

Learn More About VBA

The code you see in the box above is what was created when you recorded your macro. 

Create a Button To Get Started With VBA

To insert a button element, navigate to the Developer tab. 

Select ActiveX Command Button from the dropdown next to Insert in the Controls section.

Add Code To Give The Button Functionality

VBA coding doesn’t take place in the Excel interface. It is done in a separate environment.  

Go to the Developer tab and make sure Design Mode is active.

Looking at the code in the screenshot below, notice the beginning (Private Sub) and end (End Sub) of the code is already there.

The code below will drive the currency conversion procedure.

ActiveCell.Value = (ActiveCell * 1.28)

The screenshot below shows you how the code looks in the VBA window after you insert it .

Did It Work?

Change Color Of Background Cell Using Rgb In Vba Excel

Excel VBA RGB

VBA RGB, where RGB can be expanded by Red, Green, and Blue. And this helps in changing the color of font and cell color as well. In VBA, every color is given a value by bifurcating its shades in red, green, and blue colors. And such we have a total of 56 shades of basic colors whose RGB values range from 0 to 255.

When we apply VBA RGB, we can apply either change the color cell background or we can change the font color. Below we have the syntax of VBA RGB.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

As we can see the syntax of RGB which clearly states that for each Red, Green, and Blue, it only considers the value as Integer. And together we can count RGB values as Long data type as well. We will be seeing the implementation of the RGB in upcoming examples. Before we move ahead, let’ see one of the basic colors which we could form using RGB as shown below.

Change Color of background Cell Using RGB

We will learn how to change the color of the background cell in Excel by using the VBA Code.

You can download this VBA RGB Excel Template here – VBA RGB Excel Template

Example #1

In this example, we will see the simplest way to use RGB in VBA. Before we move forward with our Excel VBA RGB Code, first let us have data that we will be using to change the color of their cell or fonts. Below we have listed on some number from cell A1 to A10, which we will be using this example. For this, follow the below steps:

Step 2: Now in the newly opened Module write the subprocedure of VBA RGB.

Code:

Sub

VBA_RGB()

End Sub

Step 3: First we will select the cells, whose color we want to change. Here our range of cell will be from A1 to A10.

Code:

Sub

VBA_RGB() Range("A1:A10").

End Sub

Step 4: Now to change the color fonts of the selected cells, we will use the FONT function followed by COLOR.

Code:

Sub

VBA_RGB() Range("A1:A10").Font.Color

End Sub

Step 5: Now we will be using RGB with some random values to change the color Fonts from cell A1 to A10. We are giving more weightage to Red color and lesser to Green and Blue respectively as shown below.

Code:

Sub

VBA_RGB() Range("A1:A10").Font.Color = RGB(200, 100, 50)

End Sub

Example #2

In this example, we will be using the same data set which we saw in example-1 which is located in cell A1 to A10. Earlier we have seen the code to change the font colors but here we will be seeing how to change the color of a cell or background color or interior color. For this, again we can use the same line of code and do some editing or we can use a new module. We will be using the fresh module here. For this, follow the below steps:

Step 1: Open a new module and write the subprocedure for VBA RGB.

Code:

Sub

VBA_RGB2()

End Sub

Step 2: Now select the same sets of cell range from A1 to A10.

Code:

Sub

VBA_RGB2() Range("A1:A10").

End Sub

Step 3: To change the interior color, we will be using the INTERIOR function along with COLOR in the same format which we saw in Example-1.

Sub

VBA_RGB2() Range("A1:A10").Interior.Color

End Sub

Step 4: Now we will be using RGB to fill the new color to the selected cell. We will be now giving more weightage to Green color and lesser to Red and Blue colors respectively.

Code:

Sub

VBA_RGB2() Range("A1:A10").Interior.Color = RGB(0, 255, 50)

End Sub

Step 6: Let’s try to change and put the value of Green color more than 255 and see what color we get.

Code:

Sub

VBA_RGB2() Range("A1:A10").Interior.Color = RGB(0, 400, 50)

End Sub

Step 7: Now again run this updated code. We will notice that interior color selected cells A1 to A10 remains unchanged like previous, which is because RGB value resets to 255 if we use value more than 255.

Pros of VBA RGB

It is so easy to change the font color and interior color using RGB with the help of code shown in the above examples.

We can create and fill any type of color in which we want to see a font or in the cell.

Using RGB we can automate the process of changing the color of cells or fonts which is quite useful to identify the difference of data.

Things to Remember

We can try any type of combination of Red, Green, and Blue colors in RGB to see different shades of color.

We can form any type of color using RGB in Excel VBA.

The value of any color parameter can only be between o and 255. If we try to use value more than 255, it would be reset to 255 again.

Always save the file in the Macro enable format to retain the written code to be used again multiple times.

Recommended Articles

This is a guide to the VBA RGB. Here we discuss how to change the color of background cell in Excel by using the VBA Code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Use Vlookup And Hlookup In Excel? – Webnots

How Does VLOOKUP Work in Excel?

VLOOKUP is probably one of the most used Excel functions. We use VLOOKUP when we have a lookup value and we need to extract that same corresponding value in some other column. This function is mostly used in the situations when you are looking up for matching data in the leftmost (first) column. When that data is found then you go right hand side to get the exact value you wanted.

Let us take an example Excel table like below:

VLOOKUP Example

In this example, the percentage of marks are given for various students of your class and you need to find out what your percentage is. There are two ways to do this – either you can check by going through the ‘Roll No.’ Column or going to through the ‘Name’ column.

First of all, you go to the list and start looking your name from top to bottom.

Then as soon as you spot your name then you move your fingers and eyes towards the right to see your percentage of marks obtained.

And this is what VLOOKUP does for you within a fraction of seconds, the only thing you need is the syntax or formula to get your value right.

Syntax for VLOOKUP in Excel

Below is the syntax of VLOOKUP, ensure to start the formula with = sign:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

table_array: This is the whole data given in the table or entire table in which you are going to find out the value.

col_index_num: It is a column index number in which you are going to fetch the matching value.

range_lookup: This is the part where you want the approximate value (True or 1) or Exact match (False or 0).

How to Use VLOOKUP in Microsoft Excel?

There are many formats you can look up values in Excel, depending upon the size of the table. But here we are going to show you in the most effective and the widely used format. First of all, make a table in your Excel sheet. You can take any table, here we are making our own table in 3 columns (Product chúng tôi Product Name, status) as shown below:

Table for VLOOKUP

In above table, we can see that ‘Product S.No.’ column is the column which has the value that we know. But let’s assume that in status column we don’t know about the “In stock” and “Out of stock” status of the product. So, we need to find whether the product is “in stock” or “out of stock” quickly. Let us make two columns for the result (Product chúng tôi & Status) in which we enter the Product chúng tôi and get to know whether it is “in stock” or “out of stock”.

Result of VLOOKUP

Now, let us enter the ‘Product S.No.’ value ‘p001’ in the cell B10. We have to enter the formula for VLOOKUP which is =VLOOKUP(B10,A1:C6,3,FALSE) in the cell C10.

Formula for VLOOKUP

You can see the stock status for the product ‘p001’ (cell B10) is displayed as ‘In stock’ in the cell C10.

How Does HLOOKUP Work in Excel?

HLOOKUP function is not as widely used as the VLOOKUP function but it is quite similar to VLOOKUP. It is helpful when you are looking for a data in a horizontal row. When the data is found, you go down in that column and fetch the value from a cell. It will return the resultant value to you .

Syntax for HLOOKUP in Excel

Below is the syntax of HLOOKUP, ensure to start the formula with = sign:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

table_array: This is the whole data given in the table or entire table in which you are going to find out the value.

row_index_num: It is a row index number in which you are going to fetch the matching value.

range_lookup: This is the part where you want the approximate value (True or 1) or Exact match (False or 0).

As you can see, the only difference between VLOOKUP and HLOOKUP is that VLOOKUP uses ‘col_index_num’ for fetching the matching value while HLOOKUP uses ‘row_index_num’ for getting the value.

How to Use HLOOKUP in Microsoft Excel?

Using HLOOKUP in Excel is as same as VLOOKUP, you just need to put the right formula in the cell and get the result right there. Let us learn this with a simple example.

Make a table in your Excel sheet, add some data to it. Our example table looks like below:

HLOOKUP Example Table

Now when you have your table, make a ‘Result’ cell. In this cell, you’ll see your result. Write a formula like =VLOOKUP(“HomePod”,B1:D3,2,FALSE) in the cell B6 under ‘Results’.

Formula of HLOOKUP

Here we are looking for the data in the second row but it is fetched through a column ‘HomePod’. Hence, we get $70 as a result. It’s same as VLOOKUP, the only difference is that we looking the data horizontally and getting our value.

Result of HLOOKUP with Data

Remember, when you provide the direct values like HomePad, you should enter the value inside double quotes. If you provide the cell reference as B1 then you can enter the cell number without quotes. We have used this cell reference in VLOOKUP example.

Conclusion

Finding a value with respect to a reference data is hard in larger Excel sheets. Microsoft Excel provides VLOOKUP and HLOOKUP functions to lookup the required values easily. In above guide, we have learned how to use VLOOKUP and HLOOKUP in Microsoft Excel. Make sure you are putting the formula carefully, otherwise your result may not be correct.

Update the detailed information about How To Use Resize Property In Excel Vba Programming? on the Tai-facebook.edu.vn website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!