PDA

View Full Version : Data validation with dynamic range



starsky
07-03-2009, 08:52 AM
Hello,

I have a data that will be split into seperate worksheets in the same book. To these new sheets I will be applying macros. This is fine so far, I have macros that work across all the sheets.

What I want to do is insert some code that will apply data validation to all the sheets to a particular column ("O" from 010). As the number of records will vary from sheet to sheet, I'm presuming a dynamic range will need to be used - I want the validation limited to data rows.

Here is what I have so far. It gives me a run time error 91 at 'With .Validation'.

Dim rList As String
Dim Rge As Range
rList = "$Z$11:$Z$19"

'define dynamic range in col O
ActiveWorkbook.Names.Add Name:="Rge", RefersToR1C1:= _
"=OFFSET(WS.Name!R1C15,0,0,COUNTA(WS.Name!C15),1)"


With Rge
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With

Any pointers greatly appreciated. Thanks.

Bob Phillips
07-03-2009, 09:01 AM
Try


With Range("Rge")
...


You might need to preface with the sheet object as well.

BTW, Can't COUNTA(WS.Name!C15) only be 0 or 1?

starsky
07-03-2009, 12:08 PM
Thought I tried coding it that way in one of my attempts but I'll give a try.

COUNTA - numeric or text? Or am I misunderstanding your question? The C15 is from R1C1 referencing, from some recorded code. I should replace that.

Bob Phillips
07-03-2009, 12:13 PM
COUNTA - numeric or text? Or am I misunderstanding your question? The C15 is from R1C1 referencing, from some recorded code. I should replace that.

My error, I didn't notice R1C1, so it is the whole column, so no probs.