Solved

Delphi: data validation in Excel, Formula1 not fix

Posted on 2013-11-21
4
838 Views
Last Modified: 2013-11-21
Hi

I want to validate some cells (accept only values from a list).
            ExcelApp.Range['A1','A1'].EntireColumn.Validation.Add(Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:='=$D$1:$D$45');

Open in new window

With Delphi I fill in the values in column D. The number of values differs, so I can not set "Formula1:='$D$1:$D$45'" (45 may be different each time). How can I solve this that $D$45 is variable?

Thanks, Peter
0
Comment
Question by:formi
  • 2
  • 2
4 Comments
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39666694
Create a dynamic range name for your list of valid entries.

Assuming list on Sheet1 and nothing but list of entries in column D, the following can be used in the name manager to create a dynamic range:

=OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D:$D),1)

You would then change your VBA to refer to the Named Range.

Thanks
Rob H
0
 

Author Closing Comment

by:formi
ID: 39667306
As I have the german version of Excel I had to change the , by ;

now it works with the following call (thank you for your help!):

ExcelApp.ActiveWorkbook.Names.Add('artgr', '=neu_nouveaux!$F$1:OFFSET(neu_nouveaux!$F$1;0;0;COUNTA(neu_nouveaux!$F:$F);1)');

I tried it with the param-names (Name:= and RefersToR1C1:=) inside as in other functions but I got an error "error in formula". Maybe you now why?? See below

ExcelApp.ActiveWorkbook.Names.Add(Name='artgr', RefersToR1C1:='=neu_nouveaux!$F$1:OFFSET(neu_nouveaux!$F$1;0;0;COUNTA(neu_nouveaux!$F:$F);1)');
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39667624
The OFFSET function should replace the whole if the Refers to formula, not $F$1:OFFSET(...).

The offset function creates the range.
0
 

Author Comment

by:formi
ID: 39668162
ok, when I use OFFSET only it works if I use RefersTo (not RefersToR1C1. Thanks
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now