Setting Data Validation Programmatically

[LoDawg]

I have a drop down in my spread sheet and the list is created from a
range on the spread sheet. The formula is : "=BI3:$BL3". When users
import from a previous version of the software some line change the
fomula to something like "=BI27:$BL27" I have been trying to check the
lines and reset their formula1 to the correct range however I keep
getting error 450
"Wrong number of arguments or invalid property assignment"
Does anyone know what I am doing wrong, or is there a better way of
correcting the problem.
I am using vb6.
The line that produces the error:
thisWb.Sheets("Project Sheet").Range(TYPE_COL & r).Validation.Formula1
= "=BI3:$BL3"

 

[Tom Ogilvy]

Unless there is a specific reason, you need to use absolute references.
Also, if you use relative references, the formula is interpreted relative to
the activecell. This could be causing the formula to be invalid.

Just a guess, but try selecting the cell where you are applying the
formatting prior to applying it.

 

[LoDawg]

Tom,
I tried selecting the cell and got a select method failed 1004 error
so I selected the sheet then the cell and the selection worked but I am
now back to the original error. There is a hidden sheet in the work
book with templates for the row types we are adding. The end effect is
a multi tiered framework for project cost estimation. Is there away via
vb6 code to force an absolute reference.

 

[Tom Ogilvy]

Demo'd from the immediate window:

?Activesheet.Cells(5,9).Address(1,1,xlA1,True)
[Book1]Sheet1!$I$5
? Activesheet.Cells(5,9).Address(1,1,xlA1,False)
$I$5