Sub cal_24k()
Dim sheet_name As String
Dim r1, flag As Integer

sheet_name = InputBox("please input sheetname")
flag = 0

For i = 1 To Sheets.Count
 If sheet_name = Sheets(i).Name Then
 flag = 1
  Exit For
  End If
 Next
  
If flag = 0 Then
MsgBox ("NOT the  sheetsname¤J!")
Exit Sub
End If



With Worksheets(sheet_name)

.Activate

 r1 = .Range("d20").End(xlUp).Row
 If .Cells(5, 6) = "" Then
 MsgBox ("¦¹ Sorry,it is doing!")
 Exit Sub
 End If
 
 
 
For r = 5 To r1
 
Select Case Trim(Cells(r, 3))
Case "18k"
.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100
.Cells(r, 8) = Round(.Cells(r, 7) / 0.753, 2)
.Cells(r, 10) = .Cells(r, 5) - .Cells(r, 8)
.Cells(r, 11) = Round(.Cells(r, 10) / .Cells(r, 5) * 100, 2)

Case "18K"
.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100
.Cells(r, 8) = Round(.Cells(r, 7) / 0.753, 2)
.Cells(r, 10) = .Cells(r, 5) - Cells(r, 8)
.Cells(r, 11) = Round(.Cells(r, 10) / .Cells(r, 5) * 100, 2)


Case "18KL"
.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100
.Cells(r, 8) = Round(.Cells(r, 7) / 0.756, 2)
.Cells(r, 10) = .Cells(r, 5) - Cells(r, 8)
.Cells(r, 11) = Round(.Cells(r, 10) / .Cells(r, 5) * 100, 2)

Case "18kl"
.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100
.Cells(r, 8) = Round(.Cells(r, 7) / 0.756, 2)
.Cells(r, 8) = Round(.Cells(r, 7) / 0.756, 2)
.Cells(r, 10) = .Cells(r, 5) - Cells(r, 8)
.Cells(r, 11) = Round(.Cells(r, 10) / .Cells(r, 5) * 100, 2)


Case "14k"
.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100
.Cells(r, 8) = Round(.Cells(r, 7) / 0.588, 2)
.Cells(r, 10) = .Cells(r, 5) - Cells(r, 8)
.Cells(r, 11) = Round(.Cells(r, 10) / .Cells(r, 5) * 100, 2)

Case "14K"
.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100
.Cells(r, 8) = Round(.Cells(r, 7) / 0.588, 2)
.Cells(r, 10) = .Cells(r, 5) - Cells(r, 8)
.Cells(r, 11) = Round(.Cells(r, 10) / .Cells(r, 5) * 100, 2)

Case "9K"
.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100
.Cells(r, 8) = Round(.Cells(r, 7) / 0.377, 2)
.Cells(r, 10) = .Cells(r, 5) - Cells(r, 8)
.Cells(r, 11) = Round(.Cells(r, 10) / .Cells(r, 5) * 100, 2)

Case "9k"
.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)  '´£¯Â¦^¦¬
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100   '¦¨¦â
.Cells(r, 8) = Round(.Cells(r, 7) / 0.377, 2)
.Cells(r, 10) = .Cells(r, 5) - Cells(r, 8)      '  ·l¯Ó
.Cells(r, 11) = Round(.Cells(r, 10) / .Cells(r, 5) * 100, 2) '·l¯Ó²v

Case ""
.Cells(r, 7) = ""


Case Else

.Cells(r, 7) = Round(.Cells(r, 6) * 0.998 * 0.997, 2)
.Cells(r, 9) = .Cells(r, 7) / .Cells(r, 5) * 100
.Cells(r, 8) = "many me!"
End Select

Next

End With

End Sub