Using Excel Indirect Function in VBA

Watch the video on using Excel Indirect function in VBA to analyze data quickly and easily. In this video we show how to use the Excel Indirect function to create dependent lists:

Watch this video on YouTube.

The INDIRECT function returns a reference to a range specified by a text string.

The syntax of the INDIRECT function is: INDIRECT(ref_text, [a1])

ref_text part of the syntax is required. It can refer to a cell which contains an A1-style reference, a named range, a text string or an R1C1 range. If we don’t use a valid reference then INDIRECT function gives the error #REF!

Using ‘a1’ is optional. It is a logical value which defines the reference style in the cell ref_text. If a1 is true or omitted then ref_text is taken as an A1-style reference. If a1 is written as FALSE then ref_text is assumed an R!C1-style reference.

Here’s the complete VBA code on how to create dependent lists using the INDIRECT function:

Sub myIndirect()

Range(“A1:D4”).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
False
Range(“f2”).ClearContents
Range(“F2″).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=Departments”
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
End With

End Sub

Sub myIndirect2()

Range(“G2”).ClearContents
Range(“G2″).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=indirect(F2)”
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
End With

End Sub

One thought on “Using Excel Indirect Function in VBA

  1. Pingback: Using Vlookup with Indirect Function in VBA | Excel VBA Training Online

Leave a Reply

Your email address will not be published. Required fields are marked *