I'm trying to get this Evaluate macro to function properly. I'm not very familiar with programming macros, only for very basic tasks. The macro comes from the book "Excel by Example: A Microsoft® Excel Cookbook for Electronics Engineers" by Aubrey Kagan. The macro is suppose to evaluate, based on the rest of the workbook, a resistor's value based on a 4 band resistor. I am having errors when trying to run the macro and I would also like to know how to get the macro to evaluate 5 and 6 band resistors. The rest of the workbook is already setup to enter 4, 5, and 6 band resistors, it's just that the Evaluate macro is not setup for 5 and 6 band resistors and the current Evaluate macro is giving me problems. The code came straight out of the book. Any help is greatly appreciated.
The error I get is Compile Error: Case Without Select Case and the code is below.
Thanks,
Matt
Sub Evaluate()
'
' Evaluate Macro
'
Case 4
'4 bands
nLegalValue = 0
'preset value to indicate that the output is legal
'if set to 1 then this is an illegal value
If Range(“d7”).Value < 10 Then
nForward = Range(“d7”).Value * 10
If Range(“f7”).Value < 10 Then
nForward = nForward + Range(“f7”).Value
If FindValueA(nForward) = 1 Then
‘found Value
Else
nLegalValue = 1
End If
Else
'second digit gold or silver
nLegalValue = 1
End If
Else
nLegalValue = 1
'to indicate an illegal value
End If
If nLegalValue = 1 Then
Range(“result”).Value = “Forward value not found”
Else
If Range(“h7”).Value = 10 Then
'silver
nForward = nForward * 0.01 & “R”
Else
If Range(“h7”).Value = 11 Then
'gold
nForward = nForward * 0.1 & “R”
Else
'any other value
nForward = nForward * 10 ^ Range(“h7”).Value
If nForward < 1000 Then
nForward = nForward & “R”
Else
If nForward < 1000000 Then
nForward = nForward / 1000 & “K”
Else
nForward = nForward / 1000000 & “M”
End If
End If
End If
End If
'now for the tolerance
Select Case Range(“j7”).Value
Case 11:
'gold
Range(“result”).Value = nForward & “ 5%”
Case 10:
'silver
Range(“result”).Value = nForward & “ 10%”
Case 1:
'brown
Range(“result”).Value = nForward & “ 1%”
Case 2:
'red
Range(“result”).Value = nForward & “ 2%”
Case 5:
'green
Range(“result”).Value = nForward & “ 0.5%”
Case 6:
'blue
Range(“result”).Value = nForward & “ 0.25%”
Case 7:
'violet
Range(“result”).Value = nForward & “ 0.1%”
Case Else:
Range(“result”).Value = nForward & “ ??%”
End Select
End If
'now for reverse
nLegalValue = 0
If Range(“j7”).Value < 10 Then
nForward = Range(“j7”).Value * 10
If Range(“h7”).Value < 10 Then
nForward = nForward + Range(“h7”).Value
If FindValueA(nForward) = 1 Then
'found Value
Else
nLegalValue = 1
End If
Else
'second digit gold or silver
nLegalValue = 1
End If
Else
nLegalValue = 1
'to indicate an illegal value
End If
If nLegalValue = 1 Then
Range(“result2”).Value = “Reverse value not found”
Else
If Range(“f7”).Value = 10 Then
'silver
nForward = nForward * 0.01 & “R”
Else
If Range(“f7”).Value = 11 Then
'golde
nForward = nForward * 0.1 & “R%”
Else
'any other value
nForward = nForward * 10 ^ Range(“f7”).Value
If nForward < 1000 Then
nForward = nForward & “R”
Else
If nForward < 1000000 Then
nForward = nForward / 1000 & “K”
Else
nForward = nForward / 1000000 & “M”
End If
End If
End If
End If
'now for the tolerance
Select Case Range(“d7”).Value
Case 11:
'gold
Range(“result2”).Value = nForward & “ 5%”
Case 10:
'silver
Range(“result2”).Value = nForward & “ 10%”
Case 1:
'brown
Range(“result2”).Value = nForward & “ 1%”
Case 2:
'red
Range(“result2”).Value = nForward & “ 2%”
Case 5:
'green
Range(“result2”).Value = nForward & “ 0.5%”
Case 6:
'blue
Range(“result2”).Value = nForward & “ 0.25%”
Case 7:
'violet
Range(“result2”).Value = nForward & “ 0.1%”
Case Else:
Range(“result2”).Value = nForward & “ ??%”
End Select
End If
End Sub