Help with some code please
 

My sheet ('Purchases') contains a list of medicines, the code below provides
me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1, 0).Value
= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock and apply
that list to the next 20 blank cells in column A on sheet 'Medicine Record'.

My problem is that I want to have the Change Event of that sheet work when
an item in the data validation list is picked and work has Excel '97 and I
believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the control, is it
possible to do this by macro??

Thanks in advance.

Gareth

Help with some code please
 

Gareth

What do you mean by this line?

A previos workaround was to enter the items directly into the control, is
it
possible to do this by macro??


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


"Gareth" wrote in message
...
My sheet ('Purchases') contains a list of medicines, the code below
provides
me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1,
0).Value
= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock and
apply
that list to the next 20 blank cells in column A on sheet 'Medicine
Record'.

My problem is that I want to have the Change Event of that sheet work when
an item in the data validation list is picked and work has Excel '97 and I
believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the control, is
it
possible to do this by macro??

Thanks in advance.

Gareth