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.

Excel If Statement

Discussion in 'Business Applications' started by JoeHart, Dec 19, 2010.

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

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    I am trying to write a macro that compares two cells to see if they are equal. If they are equal it will copy a file (0-0.lis) from one folder (C:\Monthly_Results) to another. The file changes depending on which cell equals cell A13. I think I am close, but not sure.Sub surphace()Set r1 = Range("A1")Set r2 = Range("A2")Set r3 = Range("A3")Set r4 = Range("A4")Set r1 = Range("A5")Set r2 = Range("A6")Set r3 = Range("A7")Set r4 = Range("A8")Set r1 = Range("A9")Set r2 = Range("A10")Set r3 = Range("A11")Set r4 = Range("A12")Set r1 = Range("A13")If r1.Value = r13.Value ThenMy.Computer.FileSystem.CopyFile( _ "C:\Monthly_Results\0-0.lis",_ " C:\Aaron_Staley\0-0.lis", _ FileIO.UIOption.AllDialogs, _ FileIO.UICancelOption.DoNothing)End IfEnd Sub
     
  2. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    I don't know whether I can help with the actual macro writing, but I can give you one tip. That is impossible to read, and that makes it doubly hard to help. You should keep the lines of your code intact, and should place it inside of code boxes. (to put something into a code box place [code ] and [/code ] (without the spaces in the brackets) around it. Like this:
    Code:
    Sub surphace()
    Set r1 = Range("A1")
    Set r2 = Range("A2")
    Set r3 = Range("A3")
    Set r4 = Range("A4")
    Set r1 = Range("A5")
    Set r2 = Range("A6")
    Set r3 = Range("A7")
    Set r4 = Range("A8")
    Set r1 = Range("A9")
    Set r2 = Range("A10")
    Set r3 = Range("A11")
    Set r4 = Range("A12")
    Set r1 = Range("A13")
    
    If r1.Value = r13.Value Then
        My.Computer.FileSystem.CopyFile( _    "C:\Monthly_Results\0-0.lis",_    "  C:\Aaron_Staley\0-0.lis", _    FileIO.UIOption.AllDialogs, _     FileIO.UICancelOption.DoNothing)
    End If
    End Sub
    
    I would suggest that you use cells rather than range when comparing single cells. Cells("A1") and Cells("1,1") mean the same as your range ("A1")
    As near as I can tell the first section does nothing useful at all. R1 gets set at different stages to A1, A5, A9 and A13, but is only dealt with at the end. So you might as well just set it to A13.

    R13 isn't defined anywhere in the subroutine at all, so unless it's set elsewhere the final comparison will never be true. Wouldn't it be easier to decide which two cells you want to compare and simply say (example for A1 and A13)
    Code:
    if cells(1,1).value = cells(13,1).value then
    
    ?
     
  3. JoeHart

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    That sounds great. I will fix that. Do you know how to do the copy section?
     
  4. JoeHart

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    I tried to put the tags on the message, but for some reason my computer is blocking me from selecting the tags. It might be the security software my company has installed on my computer, but not sure. The software kept popping up security warnings, that I have learned to ignore. Thanks for the help though, I will definately use that once I figure out the rest.
     
  5. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
  6. JoeHart

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    Sorry Tags still not working I don't even see an option for tags, but I tried the following:Sub Macro9() FileCopy("C:\Monthly_Results\0.0.lis","C:\Aaron_Staley\0-0.lis")End SubI must have some of the code wrong because it is asking for an = , if I do it without the "" it asks me for list seperator or )
     
  7. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    Have you tried it without the parentheses? I know it doesn't make sense, but in all the examples I'm seeing there are no parentheses.
     
  8. JoeHart

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    This one does not work either. I get an error. "Expected ="


    #Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 12/19/2010 by hartj432
    '
    Dim SourceFile, DestinationFile As String
    SourceFile = "C:\Monthly_Results\0.0.lis" ' Define source file name.
    DestinationFile = "C:\Aaron_Staley\0-0.lis" ' Define target file name.
    FileCopy(SourceFile, DestinationFile) ' Copy source to target.

    End Sub#
     
  9. JoeHart

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    I tried it both ways with and without parentheses.
     
  10. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    I'm afraid that I can't presently access Microsoft Office, using OpenOffice on this machine and it's macros are annoyingly different from the Office ones. However I'm pretty sure that your problem is to do with the parenthases. See here.

    That is you would be changing
    FileCopy(SourceFile, DestinationFile)
    to
    FileCopy SourceFile, DestinationFile
    in your code.

    Or from the same page,
    call FileCopy(SourceFile, DestinationFile)
     
  11. JoeHart

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    Think I finally figured it out
    #Sub Macro1()'' Macro1 Macro' Macro recorded 12/19/2010 by hartj432If Cells(3, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-0.lis", "C:\Aaron_Staley\0-0.lis"ElseIf Cells(4, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-1.lis", "C:\Aaron_Staley\0-1.lis"ElseIf Cells(5, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-2.lis", "C:\Aaron_Staley\0-2.lis"ElseIf Cells(6, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-3.lis", "C:\Aaron_Staley\0-3.lis"ElseIf Cells(7, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-4.lis", "C:\Aaron_Staley\0-4.lis"ElseIf Cells(8, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-5.lis", "C:\Aaron_Staley\0-5.lis"ElseIf Cells(9, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-6.lis", "C:\Aaron_Staley\0-6.lis"ElseIf Cells(10, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-7.lis", "C:\Aaron_Staley\0-7.lis"ElseIf Cells(11, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-8.lis", "C:\Aaron_Staley\0-8.lis"ElseIf Cells(12, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-9.lis", "C:\Aaron_Staley\0-9.lis"ElseIf Cells(13, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-10.lis", "C:\Aaron_Staley\0-10.lis"ElseIf Cells(14, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-11.lis", "C:\Aaron_Staley\0-11.lis"ElseIf Cells(15, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-12.lis", "C:\Aaron_Staley\0-12.lis"ElseIf Cells(16, 3).Value = Cells(13, 3).Value Then FileCopy "C:\Monthly_Results\0-13.lis", "C:\Aaron_Staley\0-13.lis"End IfEnd Sub#
     
  12. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    Well I can tell you now there's something wrong with "ElseIf Cells(13, 3).Value = Cells(13, 3).Value Then"
    I won't insult your intelligence by telling you what.

    Here is the code that I would be inclined to use (though it has the above problem still in it). I haven't got Excel handy so I can't actually test it, but it should be all correct.

    Code:
    #Sub Macro1()
    For X = 1 to 13
        If Cells(X+3,3).value = Cells(13,3).value Then
            filecopy "C:\Monthly_Results\0-" + ltrim(cstr(X)) + ".lis", "C:\Aaron_Staley\0-11" + ltrim(CStr(X)) + ".lis"
            exit for
        End If 
    Next X
    End Sub#
    
    The question about Tags is slightly confusing because the word itself is a bit ambiguous. When you start a thread you are given the option to create tags for your thread, which will be used with search engines. But I'm talking, and I believe that you are too, about formatting tags.

    We can't use HTML on these forums because it becomes too easy for naughty people to mess things up. Instead we use what's called BBcode, which uses the same sort of principle. However where HTML might have <> as in <img> we would have to use []. To place a section of your post in a code box you would simply place the word code in square brackets at the start. Then at the end of the post you would place /code in square brackets.

    Code:
    If you click the quote button on the bottom right of my post, you will see a quote tag around the whole thing and my code tags here and around my code
    The alternative is to click go Advanced where you can simply select the text you want to put in the box and click the # button.
     
  13. JoeHart

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    Code:
    Sub Macro1()'' Macro1 Macro' Macro recorded 12/19/2010 by hartj432If Cells(3, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-0.lis", "C:\Aaron_Staley\0-0.lis"ElseIf Cells(4, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-1.lis", "C:\Aaron_Staley\0-1.lis"ElseIf Cells(5, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-2.lis", "C:\Aaron_Staley\0-2.lis"ElseIf Cells(6, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-3.lis", "C:\Aaron_Staley\0-3.lis"ElseIf Cells(7, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-4.lis", "C:\Aaron_Staley\0-4.lis"ElseIf Cells(8, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-5.lis", "C:\Aaron_Staley\0-5.lis"ElseIf Cells(9, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-6.lis", "C:\Aaron_Staley\0-6.lis"ElseIf Cells(10, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-7.lis", "C:\Aaron_Staley\0-7.lis"ElseIf Cells(11, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-8.lis", "C:\Aaron_Staley\0-8.lis"ElseIf Cells(12, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-9.lis", "C:\Aaron_Staley\0-9.lis"ElseIf Cells(13, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-10.lis", "C:\Aaron_Staley\0-10.lis"ElseIf Cells(14, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-11.lis", "C:\Aaron_Staley\0-11.lis"ElseIf Cells(15, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-12.lis", "C:\Aaron_Staley\0-12.lis"ElseIf Cells(16, 3).Value = Cells(13, 3).Value Then    FileCopy "C:\Monthly_Results\0-13.lis", "C:\Aaron_Staley\0-13.lis"End IfEnd Sub 
     
  14. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    Well done, You've got the code box worked out. Unfortunately because the whole code is still on one line it isn't particularly easy to read. The fix is easy enough: you just have to press enter after each line, or copy the code directly from the editing window where it has line breaks.

    You still have a line that essentially reads If Cells(13, 3).Value = Cells(13, 3).Value Then
    That will cause you problems; it's as daft as writing "If Ent's name is Ent's name then do such and such"! It is sometimes actually a useful effect!! but in this case I don't believe that's what you're after. It will prevent any chance of results 11, 12, or 13 ever being copied.

    Anyway It's way too late for me to be awake here. If you have any other problems that you can't figure out then I'll be back tomorrow.
     
  15. JoeHart

    JoeHart Thread Starter

    Joined:
    Aug 17, 2008
    Messages:
    125
    Thanks for pointing that out
    Code:
    #If Cells(13, 3).Value = Cells(13, 3).Value Then#[code/].  I will have to look at it tomorrow, but I think i went too far and should have stoped at 12,3.  As far as the code.  I am almost positive that I did not have everything on one line when I posted it, but it ended out on one line.  I noticed on yours you have a # symbol at the beginning and end of the code, could this be what I am missing?  Thanks again for your help.
     
  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/969465

  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