xls2003: data validation using variables in VBA

[Vubb]

Hi guys,

got a problem here and trying to solve it but the all things I come up with do not work

So, I have one range to fill with lists (data validations) dependent from another range. The key point is how to write the variables into the formula, red marker. If I just write in a hard cell like C10 the code works, but of course that's not what I need.

I would highly appreciate your help!

Here's part of the code:
a = 0
b = 16 + cAmount
For Each cell In wRange

If a > 12 Then
​

a = 1
​

Else
​

a = a + 1
​

End If
​

col = getColString(a) 'returns the needed column as a string
cell.Select
With Selection.Validation

.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(" & col & row & ")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
​

End With
​

Next cell

 

[RoryA]

Formula1:="=INDIRECT(""" & col & row & """)"

should work.

 

[Vubb]

Hi,

thanks a lot for the fast answer.

However, another problem occured before I could even test your solution.

Having the line

cell.Select
​

I get the error Select method of range class failed.

If I realign the two lines

cell.Select
With Selection.Validation

to

With cell.Validation
​

it runs into the -With- part. Unfortunately there comes the next error in line

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="randominput (tried different solutions, obviously is independent from this one"

error displayed: Method Add of object Validation failed.

​

Do you know the origin of this problem?
I think this problem even occured once and just starting the macro again would solve it... but next time it occured again, but I can't give definite info on this: I might have changed something so it would run, but I don't remember having changed anything.

 

[RoryA]

Is your worksheet protected?

 

[Vubb]

oh forgot to add this information.

no, it is not.

 

[RoryA]

Then I would guess that your validation formula is not producing a valid range reference.

 

[Vubb]

okok, I solved the other problems:
1. I had to distinctly Activate the Sheet.
2. I did not notice that obviously in some versions of my Excel-Work the Reference-List was not named. Had to redo this.
3. Removed the Selection part and replaced it with cell.Validation
4. and most stupid: I forgot that the variable "row" did not even exist but was an "a" in my code.

-> With these changes the code in the first post did work

Thanks a lot anyway. Man, I looked hours for the fault, now I'm getting myself a beer :D