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

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

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

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

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



"Ayo" wrote:

> 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

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

"Brian B." wrote:

> 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
> .
>

Try removing the equal sign inside the quotation marks.



"Ayo" <(E-Mail Removed)> wrote in message
news:8C2455A5-01C6-448E-B51E-(E-Mail Removed)...
> 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

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.

"Barb Reinhardt" wrote:

> 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
>
>
>
> "Ayo" wrote:
>
> > 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

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.

"joel" wrote:

>
> 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)"
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193665
>
> http://www.thecodecage.com/forumz
>
> .
>

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.

--
Rick (MVP - Excel)



"Ayo" <(E-Mail Removed)> wrote in message
news:BAE64D48-274E-4248-9133-(E-Mail Removed)...
> Thanks Brian, but I don't understand the SPX statement you wrote. Is there
> a
> way you can explain further.
>
> "Brian B." wrote:
>
>> 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
>> .
>>