How do I get i to show in the formula as column 1, then 2 then 3 etc. for the five drop downs in A to E? Once the i is inside the formula " "'s it is just a plain letter i.

Sub FiveDD()

Dim i As Long
Range("A1").Select
For i = 1 To 5
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) "
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
ActiveCell.Offset(0, 1).Select
End With
Next
End Sub

Thanks.
Howard

Hi Howard,

Am Tue, 15 Apr 2014 21:33:56 -0700 (PDT) schrieb L. Howard:

How do I get i to show in the formula as column 1, then 2 then 3 etc. for the five drop downs in A to E? Once the i is inside the formula " "'s it is just a plain letter i.

if your columns have the same number of values and you don't have to
COUNTIF for each column this is all you need:

With Range("A1:E1")
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween,
Formula1:="=OFFSET($A$2,0,Column()-1,COUNTA($A$2:$A$200))"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

if your columns have the same number of values and you don't have to

COUNTIF for each column this is all you need:



With Range("A1:E1")

With .Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,

Operator:= _

xlBetween,

Formula1:="=OFFSET($A$2,0,Column()-1,COUNTA($A$2:$A$200))"

.IgnoreBlank = True

.InCellDropdown = True

.ShowInput = True

.ShowError = True

End With

End With

Regards

Claus B.

--

Another clever little gem.

Thanks Claus.

Howard

if your columns have the same number of values and you don't have to

COUNTIF for each column this is all you need:



With Range("A1:E1")

With .Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,

Operator:= _

xlBetween,

Formula1:="=OFFSET($A$2,0,Column()-1,COUNTA($A$2:$A$200))"

.IgnoreBlank = True

.InCellDropdown = True

.ShowInput = True

.ShowError = True

End With

End With





Regards

Claus B.

--

Another clever little gem, thanks Claus.

Howard

Howard,
It appears that you are trying to use dynamic lists without defining
the ranges with names. IMO, it's better to define the ranges as dynamic
and just use the names in the DV dialog. OR am I missing something
here?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wednesday, April 16, 2014 7:14:42 AM UTC-7, GS wrote:
Howard,

It appears that you are trying to use dynamic lists without defining

the ranges with names. IMO, it's better to define the ranges as dynamic

and just use the names in the DV dialog. OR am I missing something

here?



--

Garry


You are correct, but how would I pass the named ranges to the desired column DV?

Howard

On Wednesday, April 16, 2014 7:14:42 AM UTC-7, GS wrote:
Howard,

It appears that you are trying to use dynamic lists without defining

the ranges with names. IMO, it's better to define the ranges as
dynamic

and just use the names in the DV dialog. OR am I missing something

here?



--

Garry


You are correct, but how would I pass the named ranges to the desired
column DV?

Howard

Via the DV dialog? Or is there some reason you need to do this via
code?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wednesday, April 16, 2014 8:43:29 AM UTC-7, GS wrote:
On Wednesday, April 16, 2014 7:14:42 AM UTC-7, GS wrote:

Howard,



It appears that you are trying to use dynamic lists without defining



the ranges with names. IMO, it's better to define the ranges as

dynamic



and just use the names in the DV dialog. OR am I missing something



here?







--



Garry





You are correct, but how would I pass the named ranges to the desired

column DV?



Howard



Via the DV dialog? Or is there some reason you need to do this via

code?



--

Garry


Yes, I wanted to use code to do something like this from Claus' suggestion.

With Range("A1:E1,G1,J1")
** seven +/- DV's **
End With

Where I was running a loop 5 times offsetting the DV code to each column.

Howard

Yes, I wanted to use code to do something like this from Claus'
suggestion.

With Range("A1:E1,G1,J1")
** seven +/- DV's **
End With

Where I was running a loop 5 times offsetting the DV code to each
column

I see. The $ symbols need to be removed from the col labels, then, so
the formula adjusts accordingly. However, IMO, if you're designing a
solution worksheet I recommend defining those dynamic ranges and
manually 'construct' the DV lists so no code is required. If the
dynamic ranges are empty then so too will be the respective DV list.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Wed, 16 Apr 2014 09:14:58 -0700 (PDT) schrieb L. Howard:

Yes, I wanted to use code to do something like this from Claus' suggestion.

try:

Sub DropDowns()
Dim rngC As Range, myRng As Range
Dim myCnt As Long

With ActiveSheet
For Each rngC In .Range("A1:E1,G1,J1")
myCnt = WorksheetFunction.CountA(.Range(rngC, rngC.Offset(199)))
.Names.Add Name:="Col" & Chr(rngC.Column + 64), _
RefersTo:="=Offset(" & rngC.Address & ",1,," & myCnt & ")"
With rngC
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Col" & Chr(rngC.Column + 64)
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional