A Macro Case for a Good helper
Posted by Nona Sloven on March 28, 2001 9:21 AM
I have a worksheet in Excel 97. I have seven cells set up each cell with data validation in column F forming a range from F25 to F31. Each of these seven cells only accept the following words: PART, LABOR, MISCELANEOUS and TOTAL. I have another column to enter amounts (numbers). This column is made up of cells J25 to J31. I want Excel to do the following: First after selecting any of the specific words listed above in the data validated cells, for example, in cell F25; if I then enter any number in J25, this number should be multiplied in this same cell by 1.12 only when it sees the word PART in F25 (the cell on the left in the same row). The same thing should happen entering any number in any of the cells J26 thru J31 and the same word (PART) is seen in in their adyacent F26 thru F31 cells, the number entered should be multiplied by 1.12. I also want Excel to do the same if the word selected is LABOR instead of PART. In this case, the multiplication should be by 1.24 instead of 1.12. Could anybody help? Your answer will be appreciated. Thanks.
Nona
Posted by Dave Hawley on March 28, 2001 4:10 PM
Nona, I post some code fo this same question a few days back. If you tell me the name you used o when I will find it for you.
Dave
OzGrid Business Applications
Posted by Nona Sloven on March 29, 2001 4:23 AM
Dave:
I read the solution you posted for Wilma. It is an item very interesting to me too so I edited and resent it. The solution you provided seems to skip some details because it didn't work for me due to sintax error. I thought some changes in Wilma's question would lead to a better comprehension, so I reformulated the question with the idea that it be more clearly understood.
The code you sent to Wilma is this:
Right click on your sheet name tab and select "View Code", then paste in this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range, Rw As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
Set WatchRange = Range("J25:J31")
If Not Intersect(Target, WatchRange) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Rw = Target.Row
Select Case Range("K" & Rw)
Case "Shirt"
Target = Target * 0.12
Case "Shoe"
Target = Target * 0.25
End Select
Application.EnableEvents = True
On Error GoTo 0
Set WatchRange = Nothing
End If
Just add more "Case" statements if needed.
Please Dave help. I think its better to reproduce the scene in a workbook having in mind every detail. Thank you.
Nona
Posted by Dave Hawley on March 30, 2001 1:39 AM
Nona, for your specific case try this code, again by Right clicking on your sheet name tab and select "View Code", then paste in the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range, Rw As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
Set WatchRange = Range("J25:J31")
If Not Intersect(Target, WatchRange) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Rw = Target.Row
Select Case Range("F" & Rw)
Case "PART"
Target = Target * 1.12
Case "LABOUR"
Target = Target * 0.25
End Select
Application.EnableEvents = True
On Error GoTo 0
Set WatchRange = Nothing
End If
End Sub
BTW Not the other code you pasted here is missing the words: End Sub
as the last line.
Dave
OzGrid Business Applications
Posted by Nona Sloven on April 02, 2001 6:53 AM
Re: Thank you for your help. It really helped.
Dave:
Your code solved me my problem. Thank you. : Dave:
