In this article, I’ll show you how you can analyze the situation in Excel VBA if a cell contains a value. You’ll learn to analyze the situation for both any value and a particular value.
Excel VBA Analysis: If Cell Contains a Value then (Quick View)
Sub If_Cell_Contains_Value() Set Cell = Range("C12").Cells(1, 1) If Cell.Value <> "" Then MsgBox "Jennifer Marlo appeared in Physics exam." End If End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Excel VBA Analysis: If Cell Contains a Value Then Return Outputs (Step by Step Analysis)
Here we’ve got a data set with the marks of some students in Physics, Chemistry, and Mathematics in a school called Sunflower Kindergarten.
The blank cells mean that the student didn’t appear, that is, was absent in that particular exam. Now let’s analyze the situation, whether a cell contains a value or not, with this data set.
⧪ Step 1: Declaring the Cell
First, we have to declare the cell whose value we want to analyze. Let’s analyze whether Jennifer Marlo appeared in the Physics exam or not.
Therefore, we need to check whether cell C12 contains a value or not.
To do that, first, we have to declare cell C12.
The line of code to accomplish this will be:
Set Cell = Range("C12").Cells(1, 1)
⧪ Step 2: Checking If the Cell Contains a Value (Including a Particular Value)
Next, we’ve to check whether the cell contains a value or not. We’ll execute this using an If condition. The line of code will be:
If Cell.Value <> "" Then
This code will be executed if the cell contains any value. To check for a value (For example, whether it contains 100 or not), use that specific value with an Equal to symbol.
If Cell.Value <> "" Then
⧪ Step 3: Allotting the Task
Then you’ve to set up a task that’ll be executed if the cell contains a value (or a specific value).
Here, we want to display the message “Jennifer Marlo appeared in Physics exam.”. So the line of code will be:
MsgBox "Jennifer Marlo appeared in Physics exam."
⧪ Step 4: Ending the If Block
Finally, you have to declare an end to the If block.
End If
So the complete VBA code will be:
⧭ VBA Code:
Sub If_Cell_Contains_Value()
Set Cell = Range("C12").Cells(1, 1)
If Cell.Value <> "" Then
MsgBox "Jennifer Marlo appeared in Physics exam."
End If
End Sub
⧭ Output:
Run this code by clicking the Run Sub/UserForm button on the toolbar.
It’ll display the message “Jennifer Marlo appeared in Physics exam.” as the cell C12 contains a value, of 50.
Examples Involving If Cell Contains a Value Then a Specified Output with Excel VBA
We’ve learned to analyze if a cell contains a value or not in VBA. Now, let’s explore some examples to make the understanding clear.
1. Developing a Macro to Filter Out a Value If the Corresponding Cell Contains any Value then in Excel VBA
Let’s develop a Macro to filter out the students who appeared in each of the exams.
That is, we have to check the cells containing the marks of each of the subjects and see whether they contain a value or not.
If they do, then we have to sort out the name of the corresponding student.
The complete VBA code to accomplish this will be:
⧭ VBA Code:
Sub Sorting_Out_Cells_that_Contain_Values()
Starting_Cell = InputBox("Enter the Reference of the First Cell of the Filtered Data: ")
For i = 2 To Selection.Columns.Count
Range(Starting_Cell).Cells(1, i - 1) = Selection.Cells(1, i)
Next i
Count = 2
For i = 2 To Selection.Columns.Count
For j = 2 To Selection.Rows.Count
Set Cell = Selection.Cells(j, i)
If Cell.Value <> "" Then
Range(Starting_Cell).Cells(Count, i - 1) = Selection.Cells(j, 1).Value
Count = Count + 1
End If
Next j
Count = 2
Next i
End Sub
⧭ Output:
Select the data set (Including the Headers) and run this Macro.
First, you’ll be asked to enter the reference of the first cell where you want the filtered data. I’ve entered G3.
Then click OK. You’ll get the names of the students who appeared in each of the exams (Including the Headers) in a new data set starting from cell G3.
Read More: Excel VBA: If Statement Based on Cell Value (2 Practical Examples)
2. Creating a User-Defined Function to Sort Out a Value If the Corresponding Cell Contains a Specific Value then in Excel VBA
Now we’ll create a user-defined function that will return the names of the students who got a specific mark in each of the subjects.
The procedure is almost the same as the above. We have to check the cells containing the marks of each of the subjects and see whether they are equal to a specific value or not.
If they are, then we’ll return the name of the corresponding student.
The VBA code will be:
⧭ VBA Code:
Function Cells_with_Values(Rng As Range, Data As Variant)
Dim Output() As Variant
ReDim Output(Rng.Rows.Count, Rng.Columns.Count - 1)
For i = 0 To Rng.Columns.Count - 2
Output(0, i) = Rng.Cells(1, i + 2)
Next i
Count = 1
For i = 2 To Rng.Columns.Count
For j = 2 To Rng.Rows.Count
Set Cell = Rng.Cells(j, i)
If Cell.Value = Data Then
Output(Count, i - 2) = Rng.Cells(j, 1).Value
Count = Count + 1
End If
Next j
Count = 1
Next i
For i = LBound(Output, 1) To UBound(Output, 1)
For j = LBound(Output, 2) To UBound(Output, 2)
If Output(i, j) = 0 Then
Output(i, j) = ""
End If
Next j
Next i
Cells_with_Values = Output
End Function
⧭ Output:
The code creates a function called Cells_with_Values that takes two arguments, a Range and a Value.
Let’s find out the students who got 100 in each of the subjects using this function.
Select a range of cells in your worksheet and enter this function in the first cell of the range:
=Cells_with_Values(B3:E13,100)
Then press CTRL + SHIFT + ENTER (Array Formula). It’ll return an array consisting of the names of the students who got 100 in each subject, including the Headers.
3. Developing a UserForm to Extract Out a Value if the Corresponding Cell Contains any Value (Or a Specific Value) in Excel VBA
Finally, we’ll develop a UserForm to extract the names of students who appeared (or got specific marks) in some specific exams.
⧪ Step 1: Opening the UserForm
Go to the Insert > UserForm option in the VBA editor to open a new UserForm. A new UserForm called UserForm1 will be opened.
⧪ Step 2: Dragging Tools to the UserForm
Besides the UserForm, you’ll get the Toolbox. Move your cursor over the Toolbox and drag 4 Labels (Label1, Label2, Label3, Label4) and 3 ListBoxes (ListBox1, ListBox2, ListBox3) and a TextBox (TextBox1) in a rectangular shape over the UserForm.
Then drag another Label (Label5) and a TextBox (TextBox2) to the bottom left corner of the UserForm.
Finally, drag a CommandButton (CommandButton1) to the bottom right corner.
Change the displays of the Labels to Lookup Column, Return Column, Any Value or a Specific Value, Value, and Starting Cell.
Also, change the display of the CommandButton1 to OK.
⧪ Step 3: Writing Code for ListBox3
Double click on ListBox3. A Private Subprocedure called ListBox3_Click will open. Enter the following code there.
Private Sub ListBox3_Click()
If UserForm1.ListBox3.Selected(0) = True Then
UserForm1.Label4.Visible = False
UserForm1.TextBox1.Visible = False
ElseIf UserForm1.ListBox3.Selected(1) = True Then
UserForm1.Label4.Visible = True
UserForm1.TextBox1.Visible = True
End If
End Sub
⧪ Step 4: Writing Code for CommandButton1
Then double click on CommandButton1. Another Private Subprocedure called CommandButton1_Click will open. Enter the following code there.
Private Sub CommandButton1_Click() On Error GoTo Message Starting_Cell = UserForm1.TextBox2.Text Count1 = 1 For i = 1 To Selection.Columns.Count If UserForm1.ListBox1.Selected(i - 1) = True Then Range(Starting_Cell).Cells(1, Count1) = Selection.Cells(1, i) Count1 = Count1 + 1 End If Next i If Count1 = 1 Then MsgBox "Select at Least One Lookup Column.", vbExclamation Exit Sub End If Data_Selected = 0 For i = 1 To Selection.Columns.Count If UserForm1.ListBox2.Selected(i - 1) = True Then Data_Selected = i Exit For End If Next i If Data_Selected = 0 Then MsgBox "Select One Return Column.", vbExclamation Exit Sub End If Count2 = 1 Count3 = 2 For i = 1 To Selection.Columns.Count If UserForm1.ListBox1.Selected(i - 1) = True Then For j = 2 To Selection.Rows.Count Set Cell = Selection.Cells(j, i) If UserForm1.ListBox3.Selected(0) = True Then If Cell.Value <> "" Then Range(Starting_Cell).Cells(Count3, Count2) = Selection.Cells(j, Data_Selected).Value Count3 = Count3 + 1 End If ElseIf UserForm1.ListBox3.Selected(1) = True Then If Cell.Value = UserForm1.TextBox1.Text Then Range(Starting_Cell).Cells(Count3, Count2) = Selection.Cells(j, Data_Selected).Value Count3 = Count3 + 1 End If Else MsgBox "Select Either Any Value or Specific Value.", vbExclamation Exit For End If Next j Count3 = 2 Count2 = Count2 + 1 End If Next i Exit Sub Message: MsgBox "Enter a Valid Cell Reference as the Starting Cell.", vbExclamation End Sub
⧪ Step 5: Writing Code for Running the UserForm
Finally, insert a new Module from the VBA toolbar and insert the following code there.
Sub Run_UserForm()
UserForm1.Caption = "Filtering Cells that Contain Values"
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.ListStyle = fmListStyleOption
UserForm1.ListBox3.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox3.ListStyle = fmListStyleOption
For i = 1 To Selection.Columns.Count
UserForm1.ListBox1.AddItem Selection.Cells(1, i)
UserForm1.ListBox2.AddItem Selection.Cells(1, i)
Next i
UserForm1.ListBox1.MultiSelect = fmMultiSelectMulti
UserForm1.ListBox3.AddItem "Any Value"
UserForm1.ListBox3.AddItem "Specific value"
UserForm1.Label4.Visible = False
UserForm1.TextBox1.Visible = False
Load UserForm1
UserForm1.Show
End Sub
⧪ Step 6: Running the UserForm (The Final Output)
Your UserForm is now ready to use. Select the data set from the worksheet (B3:E13 here) (Including the Headers) and run the Macro called Run_UserForm.
The UserForm will be loaded in the worksheet. From the Lookup Column table, I’ve selected Physics and Mathematics, because I want to get the student names who appeared in Physics and Mathematics exams.
From the Return Column table, I’ve selected Student Name, because I want to get the student names.
And from the Any Value or Specific Value table, I’ve selected Any Value.
Finally, in the Starting Cell box, I have put G3.
Then click OK. You’ll get the names of the students who appeared in Physics and Mathematics exams starting from cell G3.
If you had selected Specific Value from the Any Value or Specific Value table, you would have got another TextBox to enter the specific value.
Here I’ve entered 100.
Click OK. And you’ll get the students who got 100 in Physics and Chemistry.
Things to Remember
We used a two-dimensional array to return the student names in the User-Defined Function described in example 2.
Conclusion
So these are a lot of things regarding analyzing if a cell contains a value or not, isn’t it? Hope the examples will make things clear for you. Still, if you have any questions, feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.