How to add data validation from range with numeric columns VBA

Question

I want to add "data validation" including a drop down menu to some cells in a Workbook that uses numeric columns. I only managed to do it with alphabetical columns.

When I execute the following code I get the error message "1004" and the macro stops at the line with the Formula1 (excel settings to numerical columns):

Sub foo_numeric()
  Dim rngTest As Range 'Range where the Data validation should be applied
  Dim wsTest As Worksheet

  Set wsTest = Worksheets("WorkSheetName")
  Set rngTest = wsTest.Range(wsTest.Cells(1, 2), wsTest.Cells(5, 2))

  With rngTest.Validation
    .Delete
    .Add Type:=xlValidateList, _
       Formula1:="=WorkSheetName!Z1S1:Z22S1"
  End With
End Sub

When I switch the settings to alphabetical columns and execute the following code, I get no error message and the macro works smoothly:

Sub foo_alphabetical()
    Dim rngTest As Range 'Range where the Data validation should be applied
    Dim wsTest As Worksheet

    Set wsTest = Worksheets("WorkSheetName")
    Set rngTest = wsTest.Range("B1:B5")

    With rngTest.Validation
     .Delete
     .Add Type:=xlValidateList, _
         Formula1:="=WorkSheetName!$A$1:$A$22"
    End With    
End Sub

Is there some error in my code that I missed? How can I use data validation with numerical columns?

Remark: To put a String with the possible options into Formula1 is not an option as the string would be too big.

`wsTest.Range(wsTest.Cells(1, 2), wsTest.Cells(5, 2))` - you need to add the worksheet to both uses of `Cells()`. Also, did you change it for ease of reading here on SO, or is the sheet literally called `WorkSheetName`? — BruceWayne, Mar 13 '19 at 15:22
Thanks, I forgot to add the worksheet to both uses of Cells(). I edited it, but it doesn't change anything about my problem. In my minimal example (that I also tested), the worksheet is literally called "WorkSheetName", but I get the same error with any other name. — julian, Mar 13 '19 at 16:22
First, what do you mean by a range of `Z1S1:Z2S51`?? Also, for a quick test, can you `Dim rngFormulaTest as Range // Set rngFormulaTest = Worksheets("WorkSheetName").Range("A1:A22") // ... // Formula1:= rngFormulaTest`. Does that throw an error? — BruceWayne, Mar 13 '19 at 16:28
Thanks to your questions I found the source of the problem. Thanks! The Format of Formula1 needs to be a string and can't be just the Range object. (https://stackoverflow.com/questions/22956604/how-to-add-data-validation-to-a-cell-using-vba) — julian, Mar 13 '19 at 17:24
I believe `Formula1` can be a range object. However, glad to see it was solved. You needed it in `R1C1` format, which is what you noticed when "translating" it from German. — BruceWayne, Mar 13 '19 at 17:30

score 0 · Accepted Answer · answered Mar 13 '19 at 17:20

Thanks to the questions of @BruceWayne I was able to solve my issue.

Solution:

Replace

Formula1:="=WorkSheetName!Z1S1:Z22S1"

with

Formula1:="=WorkSheetName!R1C1:R22C1"

Source of my Problem:

My office package is in German, so when I reference to Rows and Columns in excel, I reference to Zeilen and Spalten. That's what Z1S1:Z2S51 means. By changing my office package to English I figured out how to reference properly to numerical Columns.

Remark: Range.Adress always gives me alphabetical columns back.

How to add data validation from range with numeric columns VBA

1 Answers1