Data Validation Help Needed urgently !!!!!!!!!!!

[Ayo]

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

 

[Barb Reinhardt]

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.

 

[joel]

I think you are missing double quotes around the strings

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

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

 

[Brian B.]

For data validation you need your formula to return a True/False
value.

An example would be setting your formula to SPX:
=OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),
1)="SPX"

-Brian

 

[Ayo]

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]

Again, I say manually create a named range using your formula below
=OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)

If the value of COUNTIF(Markets,B20) = ZERO, the range won't work.


Let's look at the offset formula

=OFFSET(REFERENCE,ROWS,COLUMNS,[HEIGHT], [WIDTH])

[HEIGHT] or [WIDTH] need to be positive non-zero values for the offset
equation to work. Trust me, try creating a named range using the OFFSET
(manually) to see what the result is. Once you've got the range, type CTRL
G and the range name to see what is selected. I bet you have a problem with
[HEIGHT] or your MATCH piece.

 

[Ayo]

Thanks Brian, but I don't understand the SPX statement you wrote. Is there a
way you can explain further.

 

[JLGWhiz]

Try removing the equal sign inside the quotation marks.

 

[Ayo]

The problem I am having really is this: when I run this sub:
Private Sub Worksheet_Activate()
Rows("5:19").Hidden = True
Range("B20") = ""
Range("B3") = ""
Range("E3") = ""
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

Range("E3").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="OFFSET(SiteStart,MATCH(B3,Sites,0)-1,1,COUNTIF(Sites,B3),1)"
End With
End Sub

If I left the formula1 line like this:
Formula1:="OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)"
the code seem to work except when I look in the validation
range,Range("B3:C3"), and all I see is
OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1) not the
list of sites that I expect.

But when I write the formula1 line like this:
Formula1:="=OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)"
I get a error.

Again, I say manually create a named range using your formula below
=OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)

If the value of COUNTIF(Markets,B20) = ZERO, the range won't work.


Let's look at the offset formula

=OFFSET(REFERENCE,ROWS,COLUMNS,[HEIGHT], [WIDTH])

[HEIGHT] or [WIDTH] need to be positive non-zero values for the offset
equation to work. Trust me, try creating a named range using the OFFSET
(manually) to see what the result is. Once you've got the range, type CTRL
G and the range name to see what is selected. I bet you have a problem with
[HEIGHT] or your MATCH piece.

--
HTH,

Barb Reinhardt

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

 

[Ayo]

Thanks Joel. The problem I am getting seem to be with the "=" before the
OFFSET. When I write it like this:
Formula1:="=OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)"
I get an error. But when I write it like this:
Formula1:="OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)"
I don't get an error but there is no list in the cell I am trying to validate.

 

[Rick Rothstein]

The "SPX" was, to quote Brian, an example. The key thing you were supposed
to take from his statement is the need to assign a logical expression the
evaluates to TRUE or FALSE... your original OFFSET statement did not do
this... testing it for some return value, such as a text string like "SPX",
will do that.

 

[Ayo]

Whan I do that I get the following lines in my cell as my dropdown list.
OFFSET(MarketStart
MATCH(B20
Markets
0)-1
1COUNTIF(Markets
B20)
1)

 

[Ayo]

Rick,
Thanks for the input but my issue is still the same. I can't seem to be
able to get my point across. This gives an error "=OFFSET" and this doesn't
"OFFSET". But the one without the error still doesn't give me the list just
the string "OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),
1)" in a list form.

I think it's better if I send you the file, about 1MB, so you can see what I
am talking about.

 

[Barb Reinhardt]

Try adding this code and tell us what happens?

ThisWorkbook.Names.Add Name:="myList", _
RefersToR1C1:=
"=OFFSET(MarketStart,MATCH(Sheet1!R2C20,Markets,0)-1,1,COUNTIF(Markets,Sheet1!R2C20),1)"

on error resume next
debug.print range("myList").address

if err <> 0 then
Msgbox("There is an error in the named range")
end if
on error goto 0

 

[Ayo]

I get a "Method 'Range' of object '_Worksheet' failed" error on:
Debug.Print Range("myList").Address

 

[JLGWhiz]

1. What are you expecting to see in the validation list?
2. Can you use VBA code to produce the same result?

 

[Ayo]

I was expecting a list of numbers.
When I check the validation after run the code, I see:
OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)
and when I insert the "=" in front of "OFFSET", I get the result that I
wanted. But when I use it in the code:
Formula1:="=OFFSET(MarketStart,MATCH(B20,Markets,0)-1,1,COUNTIF(Markets,B20),1)"
I get an error.

 

[Barb Reinhardt]

OK then. What that tells me is that the range myList may not exist. Now I
need to know this

What is the address for the range MarketStart? Include worksheet
What is the value for MATCH(B20,Markets,0) - 1
What is the value for COUNTIF(Markets,B20)

I bet one or both of the last two is an error or 0.