Can't save book with new macro

[Gulfvet91]

I am trying to get a VBA Macro to work properly in Excel 2010. I am not getting any error indications but it won't save.
Below is the code:
[code]
Private Function FishWeight()

End Function

Dim W As Integer

If C3 = ("LMB") Then W = E3 ^ 2 * F3 / 1200
Else
If C3 = ("SMB") Then W = E3 ^ 2 * F3 / 1200
If C3 = ("Rainbow") Then W = E3 * F3 ^ 2 / 800
If C3 = ("Brown") Then W = E3 * F3 ^ 2 / 800
If C3 = ("Saugeye") Then W = E3 ^ 3 / 2700
If C3 = ("Walleye") Then W = E3 ^ 3 / 2700
If C3 = ("Muskie") Then W = E3 ^ 3 / 3500
If C3 = ("Northern") Then W = E3 ^ 3 / 3500

End If
[code]

[protonLeah]

Please modify your title. Then I can provide some alternatives.

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.



Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Gulfvet91]

I'm still not sure that will work. When I put in the Option Specific code, it came up flagged red. Does Option Specific mean the formula will only work on the 3rd row? That won't work for what I am trying to do. Let me put up the worksheet along with the code so you can see what I am trying to accomplish. It is attached to this post. I am trying to use this macro for numerous rows.

I also have a question about the RANGE function you sent on the last reply. How do I handle that when I need to multiply one cell by another?

[protonLeah]

Private Sub FishWeight()
Option Specific

Dim W As Integer

If Range("C3").Value = "LMB" Then W = Range("E3").Value ^ 2 / 1200
If Range("C3").Value = "SMB" Then W = Range("E3").Value ^ 2 / 1200
If Range("C3").Value="RAINBOW" Then W = Range("E3").Value. ^2/1200
If C3 = ("Rainbow") Then W = E3 * F3 ^ 2 / 800
If C3 = ("Brown") Then W = E3 * F3 ^ 2 / 800
If C3 = ("Saugeye") Then W = E3 ^ 3 / 2700
If C3 = ("Walleye") Then W = E3 ^ 3 / 2700
If C3 = ("Muskie") Then W = E3 ^ 3 / 3500
If C3 = ("Northern") Then W = E3 ^ 3 / 3500

End If

End Sub

Since I see what you are doing, you do need a FUNCTION vs. a Sub:
1. It's Option Explicit V. Specific and it should be the first line in the module
2. When you use a function, you pass values from the worksheet or another macro in a parameter list in parentheses after the function name.
3. The function must return a value to the cell it is entered in. You do this by declaring the type of the function at the end of the declaration line so that the first line of the function looks like:

Public Function FishWeight(ByVal Species as String) As Long

then in column J you have
J3: =FishWeight(C3)

The C3 in parentheses will pass the data in C3 to the function for calculations.

then the function will return the value calculated with the number in cell C3 to cell J3.

However, the function must be set to the calculated value. You held the calculated value in a variable "W" but instead you needed a line like:
FishWeight = Range("E3").Value ^ 2 / 1200; replacing every instance of "W" with "Fishweight".

Your line(s):

If Range("C3").Value = "LMB" Then W = Range("E3").Value ^ 2 / 1200, etc

should be: If Range("C3").Value = "LMB" Then FishWeight = Range("E3").Value ^ 2 / 1200

In the attachment, I used the Select Case structure instead of the IF's, thought the IF's are ok, here

[royUK]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

Add the code tags

[Gulfvet91]

ptotonLeah, I tried to incoporate your suggestions but the macro still won't save. I have attached my amended file so you can see what I ahve messed up. I hope I got those code tags right.

Please Login or Register  to view this content.

[protonLeah]

That "End Function" at the top of the macro is wrong, delete it. The End Function or End Sub, as the case may be, is always the last line of a macro. Because of the one at the top, your function is actually empty and every thing down to the last one is orphaned. You should always Debug your code every so often, that would have let you know there was a problem.

[royUK]

Are you having problems seeing my posts. Amend your first post before I close this Thread

[Gulfvet91]

I can't believe you are obsessing over mistakes in my very first post. If you will look in subsequent posts in this thread I have incorporated everything you have told me to as I went along. Give a beginner a break!

[royUK]

You were asked to edit the original post. You agreed to the Forum Rules so follow them or don't post. They apply the same to everyone & in return you get free help, not a bad bargain I would think

Thread closed until you make a decision.