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

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" wrote in message
oups.com...
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

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

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" wrote in message
oups.com...
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

Thanks for the explanation. The code works fine now.