Solved: My Ifs won't work - MS Excel 2002

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

NOxGuy

Thread Starter
Joined
Apr 28, 2010
Messages
7
Please keep in mind, I don't know Macros very well, and know nothing at all about programming with Visual Basic.
I have created a sheet with 14 Macros, some of which have Ifs. Every sheet works perfectly except for the Ifs.
Example:
If (Current_Fuel = "1") Then
Range("J39").Copy
Range("N39").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ElseIf (Current_Fuel = "2") Then
Range("K39").Copy
Range("N39").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ElseIf (Current_Fuel = "3") Then
Range("L39").Copy
Range("N39").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ElseIf (Current_Fuel = "4") Then
Range("M39").Copy
Range("N39").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

Current_Fuel is a cell name. It can contain a 1, 2, 3 or 4. The rest of the Macro works, but not the IF.
 

NOxGuy

Thread Starter
Joined
Apr 28, 2010
Messages
7
This is one of the IFs that won't work.

The Macro replaces the title and the row in the table, but does not see the "1" in N40 and enter the value form J39 into N39.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
The reason it won't work is because of the way you craeted the IF
Since it's a named range you should use the named range, I editted it to work even though I don't understand what you want but...

' Enter selected fuel into current cell (N39)
Select Case Range("Current_Fuel")
Case Is = "1"
Range("J39").Copy
Range("N39").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Case Is = "2" ' Bio-Gas is current selection
Range("K39").Copy
Range("N39").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Case Is = "3" ' entered value is HR
Range("L39").Copy
Range("N39").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Case Is = "4" ' entered value is HRM
Range("M39").Copy
Range("N39").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Select

I used the select case statement and then it simplifies the if by replacing a CAse = statement
Try it in your code.

Let me know
 

NOxGuy

Thread Starter
Joined
Apr 28, 2010
Messages
7
Hans:

I plugged it an and it worked.

Heel hartelijk bedankt. Nu kan ik mijn volledige plaat.

I really don't know the vocabulary or syntax for VB. Do you know of a guide that would help me?

Chuck
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Geen dank, graag gedaan.
There a number of sites with tips and so.
http://www.cpearson.com/Excel/Topic.aspx
http://www.rondebruin.nl/
My first steps where analysing a recorded macro and then the next steps follow.
I never really consulted a site but following different links picked up knowledge here and there.
My first steps in programming were in HP Basic in 1983 (in West Africa)

If I find something interesting I'll mail you. The two sites above will help you and offer a lot of ideas and imagination and yur own fantasy will help you further on
 

NOxGuy

Thread Starter
Joined
Apr 28, 2010
Messages
7
I have taken a quick look at both sites and the CParson site looks very promising.

I am still looking for a guide that gives the basic commands with definitions and the basic syntax, but no luck so far.

Thanks again for all the help.

Chuck
 
Joined
Oct 20, 2004
Messages
7,837
Of course, Hans! I had tried to work this out yesterday, but I am really bad with named ranges....
 

NOxGuy

Thread Starter
Joined
Apr 28, 2010
Messages
7
Thank you both for all the assistance. I still have one to go, and I will have completed this month long effort.

Chuck
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Hi Chuck,
Don't forget if this has solved it for you to mark the post as solved
Happy to know that we have been able to help you
 

NOxGuy

Thread Starter
Joined
Apr 28, 2010
Messages
7
You have probably noticed that I am new with Tech Guy and am not familiar wit the site. I will post a "Solved" when it is finished.

I have one last problem. It is a different kind of IF. In this one I have 10 radio buttons. I want the macro to discover the "TRUE" item and enter the values for the selected parameter into a table. I only put in the first 3 items as a test, but it didn't work. If I remove the IF statements, the copy and paste work fine and initial and final routines also work, but the IF does nothing. It appears to be just skipping over the IF.

If you can help me with that one, it would be much appreciated.

Chuck
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
The value is not tested on "TRUE" but just the word True or False
These are what you call boolean not strings

So:

If (Ant = True) Then
Range("J8:K8").Copy
Range("K23:K24").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=Truie

ElseIf (Bit = True) Then
Range("J9:K9").Copy
Range("K23:K24").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

etc...

Okay?

I cannot test it because the module linked to the Enter Button does not exist
 

NOxGuy

Thread Starter
Joined
Apr 28, 2010
Messages
7
I have linked the Enter button so it will run the macro M19F_Enter.

In Excel, usually on the icon bar, there is an icon called Control Toolbox. One of the items is "Option Button". That is the tool I have used to create the selection list. I am beginning to think I don't know how to use it.

I tried playing around a bit with it and used the attached test sheet to try to isolate the problem. I put a:
Range("C3").Select
ActiveCell.FormulaR1C1 = "(LHV)"
at the beginning and:
Range("C4").Select
ActiveCell.FormulaR1C1 = "(HHV)"
at the end just to see if it is executing the macro. Click on the "Enter" button and you will see that it does everything but the IF. If you look at the other test macros, you can see that I tested the copy and print portions of the IF and they work fine. I believe that the problem must be in the following lines:

If (Ant = True) Then

ElseIf (Bit = True) Then

ElseIf (Lig = True) Then


Since it is going though the macro and doing nothing in the IF, it suggests that it feels the IF is false in all three cases. Since the "TRUE" in the Ant, Bit and Lig cells is all caps, I tried putting the True in all caps in the macro. VB changes it back to lower case "rue". I tried putting TRUE in quotes ("TRUE"). That kept the true all caps, but still didn't help. I tried changing cell Bit to True, but when I entered it, Excel changed it to TRUE. I tried entering the word True into another cell, and Excel did the same thing, changed it to all caps. I tried entering any other word into another cell, and it stayed the way I entered it. I looked in auto correct to see if there was an entry for True, but there wasn't. There seems to be no way to have the TRUE in the Bit cell and the True in the IF be the same case. I don't know if it is the problem, but if it is, I am stumped. If it isn't I am stumped.

There must be a way to use the radio button control. How would anyone use it? And, everyone does use it.

Is there a different way to have a user selection of fuels and have a macro enter the values?

Chuck
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top