1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by NOxGuy, Apr 28, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. NOxGuy

    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.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    can you attach a sample with non-private data?
     
  3. NOxGuy

    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.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  5. NOxGuy

    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
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  7. NOxGuy

    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
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I had that one in my favorites list too, Thanks Slurpee
     
  10. slurpee55

    slurpee55

    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....
     
  11. NOxGuy

    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
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  13. NOxGuy

    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
     

    Attached Files:

  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  15. NOxGuy

    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
     

    Attached Files:

  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919888

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice