Using a Variable with the INDIRECT function

[Scott JS]

Am trying to write a simple program to create a validation list in select cells in a spreadsheet. (Office 2010, Windows 7)

This code works:
ActiveCell.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect($C3)"


While the code above works, I do not want an absolute reference. The code below won’t work (Run Time Error 1004 – Application Defined or object defined error).

j = ActiveCell.Row
ActiveCell.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect($C & j & )"

All I am changing is
from: Formula1:="=indirect($C3)" <-- the code that works
to: Formula1:="=indirect($C & j & )"
or: Formula1:="=indirect($C” & j & ”)"

Am I missing something in my syntax or is this a limitation with the Indirect function?

 

[Marcelo Branco]

This should work
Formula1: = "=INDIRECT($C" & j & ")"
provided there is a valid reference in the cell in column C on the same row.

M.

 

[Scott JS]

This should work
Formula1: = "=INDIRECT($C" & j & ")"
provided there is a valid reference in the cell in column C on the same row.

M.

Marcelo -- Thanks, Appreciate the suggestion. What you have there is actually one of the options I had tried/referenced in my post. Am beginning to think this is an Indirect function issue. Have never been able to get variables in VBA to work with it.

 

[Marcelo Branco]

Marcelo -- Thanks, Appreciate the suggestion. What you have there is actually one of the options I had tried/referenced in my post. Am beginning to think this is an Indirect function issue. Have never been able to get variables in VBA to work with it.

It worked for me. For example:
put in C4
A1:A3

in A1:A3
blah
bleh
blih

Then with cursor in D4 (Active cell) run this macro

Sub aTest()
    Dim j As Long
    
    j = ActiveCell.Row
    ActiveCell.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=indirect($C" & j & ")"
    End With
End Sub

M.

 

[Marcelo Branco]

Another way

Name the range A1:A3 as MyRange

Put in C4
MyRange

With cursor in D4 (ActiveCell) run the macro above

M.

 

[andiemac]

If the line
xlBetween, Formula1:="=indirect($C3)"

works in your code then the line
xlBetween, Formula1:="=indirect($C" & j & ")"

should work as well.

One thing I can say is that the quotes as shown in your post are not the standard type of quotation marks used in VBA. If the quotes in your VBA code are slanted like shown, delete them and retype them.
($C" & j & ")" not ($C” & j & ”)"

 

[Marcelo Branco]

One thing I can say is that the quotes as shown in your post are not the standard type of quotation marks used in VBA. If the quotes in your VBA code are slanted like shown, delete them and retype them.
($C" & j & ")" not ($C” & j & ”)"

Good eyes!! I hadn't noticed this.

M.

 

[Scott JS]

Good eyes!! I hadn't noticed this.

M.

Y'all, thanks for the great counsel. It works. needed to populate the referenced cell -- when it is populated, the code works, when it is blank, the code does not. Weird considering I can create and copy the validation list manually when the referenced column is blank.

Thanks for your insights.