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
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
Next cellcell.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.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(" & col & row & ")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True