Drop Down List
I think this is it.
Tested on both the DV drowdown selection and the multiply by Offset(6, 0)
codition.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit
Dim cell As Range
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value < "" Then
.Value = .Value * .Offset(6, 0).Value
End If
End With
End If
Application.EnableEvents = True
Set r = Range("A1") 'edit to suit the DV cell(s)
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit
nums = Array(1, 2, 3, 4, 5, 6) 'add more numbers
For Each rr In r
ival = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival 0 Then
rr.Value = ival
End If
Next
ws_exit:
Application.EnableEvents = True
End Sub
Gord
On Fri, 1 Aug 2008 14:15:00 -0700 (PDT), Pete
wrote:
many thanks Gord, I look forward to seeing your code
Peter
|