Command button code does not execute

[twlove]

I have a command button that when clicked is supposed to multiple the
value in R33 by 115% if cell N8 = Yes and by 130% if cell N8 = No.

What's wrong with my code?

******


Public Sub Calc_Freight()

Dim Assembly As String

Assembly = Range("N8")

Application.ScreenUpdating = False
Application.ActiveSheet.Range("R33").Select
Application.ScreenUpdating = True

Select Case ("Assembly")

Case "No"

For Each cell In Selection
cell.Value = cell.Value * 1.3
Next

Case "Yes"
For Each cell In Selection
cell.Value = cell.Value * 1.15
Next

End Select

End Sub

 

[tissot.emmanuel]

Hi,

Try:

Select Case Assembly

instead of

Select Case ("Assembly")

Best regards from France,

Manu/

 

[Guest]

You have some conflicting code....
On the one hand you seem to want to apply a factor to each cell in the
current selection...
But, within the code you select cell N33 (one cell)...then loop through all
ONE of them. (?)

If you really want to use the current selection,
try something like this:

'------------start of code---------------
Public Sub Calc_Freight()

Dim Assembly As String
Dim cCell As Range
Dim Factor

On Error GoTo errTrap

Assembly = Range("N8").Value

Select Case Assembly
Case "No"
Factor = 1.3
Case "Yes"
Factor = 1.15
Case Else
MsgBox "Neither Yes nor No!"
End Select

For Each cCell In Selection.Cells
cCell.Formula = cCell.Value * Factor
Next cCell

errTrap:
If Err.Number <> 0 Then
MsgBox _
Title:="Problems Encountered", _
Prompt:="Could not execute Calc_Freight" & vbCr & vbCr _
& "Error Number: " & Err.Number & ": " & Err.Description & " was
encountered", _
Buttons:=vbCritical + vbOKOnly
End If
End Sub
'------------end of code---------------


OTHERWISE......
If you only want to set the new value of cell N33..
Perhaps this:

'------------start of code---------------
Public Sub Calc_FreightN33()

Dim Assembly As String
Dim cCell As Range
Dim Factor

On Error GoTo errTrap

Assembly = Range("N8").Value

Select Case Assembly
Case "No"
Factor = 1.3
Case "Yes"
Factor = 1.15
Case Else
MsgBox "Neither Yes nor No!"
End Select

Set cCell = Range("N33")
cCell.Formula = cCell.Value * Factor

errTrap:
If Err.Number <> 0 Then
MsgBox _
Title:="Problems Encountered", _
Prompt:="Could not execute Calc_Freight" & vbCr & vbCr _
& "Error Number: " & Err.Number & ": " & Err.Description & " was
encountered", _
Buttons:=vbCritical + vbOKOnly
End If

End Sub
'------------end of code---------------


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP