Excel VBA: If Cell Contains Value Then Return a Specified Output

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:

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.

Related Articles

• If Statement in Excel VBA