I have the workbook level named ranges created here in this sub:

Sub defineRANGES()
Dim lRow As Long
lRow = Worksheets("Sites Task List").Cells(Rows.Count, "A").End(xlUp).Row

If lRow 2 Then
ThisWorkbook.Names.Add Name:="MarketStart", _
RefersTo:=Worksheets("Sites Task List").Range("A1")
ThisWorkbook.Names.Add Name:="Markets", _
RefersTo:=Worksheets("Sites Task List").Range("A:A")

ThisWorkbook.Names.Add Name:="SiteStart", _
RefersTo:=Worksheets("Sites Task List").Range("B1")
ThisWorkbook.Names.Add Name:="Sites", _
RefersTo:=Worksheets("Sites Task List").Range("B:B")
End If
End Sub

"Barb Reinhardt" wrote:

I'd not tried your code, but if it were me, I'd create a workbook level named
range based on this offset formula

=OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)

Then I'd enter CTRL G and type in the range name to see if it really exists.
Then, based on that, I'd update the validation list.
--
HTH,

Barb Reinhardt



"Ayo" wrote:

I am trying to write a data validation in VBA. I know I have the code right
but I can't get it to work. It works fine if the validation was done dirctly
in excel but I get an error when I try the same thing in VBA. Below is a
snippet of the code I am having a problem with. The problem seem to be with
the " Formula1:=" line. I have been scratching my head for the past 24hrs now
trying to figure this out and I am still not coming up with anything. Any
help will be greatly appreciated.

Range("B3:C3").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, _

Formula1:="=OFFSET(MarketStart,MATCH(B20,Markets,0 )-1,1,COUNTIF(Markets,B20),1)"
End With