initial value in a cell with validation

[bernd]

Hello,

I use several cells with the same validation fields. Is it possible to
create a macro that sets the initial value of a cell with validation?

Bernd

 

[Chip Pearson]

Bernd,

I am assuming you are using a List type Validation. Use code like the
following:

Sub AAA()
Dim S As String
S = Mid(Range("A1").Validation.Formula1, 2)
Range("A1").Value = Range(S)(1, 1)
End Sub

Change the "A1" to your cell reference.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[bernd]

This works, but now I want to repeat this macro for all cells with in
column J. (starting from row 5).
I edited your code, but it doesn't work. What am I doing wrong?

Sub initial_value()
Dim S As String
Dim vCell As Range
For i = 5 To 65
vCell = Cells(i, 10)
S = Mid(Range(vCell).Validation.Formula1, 2)
Range(vCell).Value = Range(S)(1, 2)
Next i
End Sub

 

[Mike Fogleman]

You Dimmed vCell As Range and then used Range(vCell), in other words the
Range of a Range, and it blows up. Take a look at this code:

Sub initial_value()
Dim S As String
Dim vCell As Range
Dim i As Integer
For i = 5 To 65
Set vCell = Cells(i, 10)
S = Mid(vCell.Validation.Formula1, 2)
vCell.Value = Range(S)(1, 1)
Next i
End Sub

You had also changed the line:
vCell.Value = Range(S)(1, 1)
to read
vCell.Value = Range(S)(1, 2)

That would have pointed the vCell Value to the first row, second column, of
the validation list. Is there a second column? If not, then you would have
returned nothing.

Mike F

 

[bernd]

Thanks for the explanation. The code works fine now.