Ask Experts Questions for FREE Help !
Ask
    rsernowski's Avatar
    rsernowski Posts: 13, Reputation: 1
    New Member
     
    #1

    Feb 2, 2009, 12:25 PM
    if statement macro
    Hello ,
    I have an excel macro to take data from one spreadsheet and format in a partciular method. Now I want to add an if statement like
    I have an excel formula that works but I need it put into the macro. The formula is:

    if(AA21="USD", "6YB", "6YA")

    Help
    Thanks
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Feb 2, 2009, 06:02 PM

    You start talking about using a macro to "format" something, but don't tell us what. Are you putting the data somewhere else? Another sheet? Another cell?

    The format issue aside, if what you're asking is how to use VBA to set the value of one cell to either 6YB or 6YA based on the value of cell AA21, then that code would be:
    Code:
    If Range("AA21").Value = "USD" Then
        Range("AB21").Value = "6YB"
        Else
        Range("AB21").Value = "6YA"
    End If
    Just change the "AB21" to the cell you actually want to adjust and insert that code into your macro.
    rsernowski's Avatar
    rsernowski Posts: 13, Reputation: 1
    New Member
     
    #3

    Feb 3, 2009, 05:53 AM
    Quote Originally Posted by JBeaucaire View Post
    You start off talking about using a macro to "format" something, but don't tell us what. Are you putting the data somewhere else? Another sheet? Another cell?

    The format issue aside, if what you're asking is how to use VBA to set the value of one cell to either 6YB or 6YA based on the value of cell AA21, then that code would be:
    Code:
    If Range("AA21").Value = "USD" Then
        Range("AB21").Value = "6YB"
        Else
        Range("AB21").Value = "6YA"
    End If
    Just change the "AB21" to the cell you actually want to adjust and insert that code into your macro.
    Hi , Thanks JBeaucaire,
    Sorry for not enough details. I export a file from an FTP site, called ticket1, to my desk top. My macro takes the data from ticket 1, separates the data using text to columns, then opens up a template, then copy and paste special all the required fields(13 fields have to be copied from ticket 1 and placed into newticket1. After the data is moved over I save the file to a specific folder , with the name a field from ticket 1(the field is always unique). I had placed the code in my macro just before it saves the file, it just skipped over it. Then I added a sub procedure at the end of the macro, still didn't work. I have attached my full macro.
    Thanks
    Robert

    I must need something else. Sorry for the
    Attached Files
  1. File Type: txt code1.txt (8.3 KB, 192 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Feb 3, 2009, 01:15 PM

    I'm looking at the macro and I don't see where you:

    a) inserted the code I suggested prior to the SaveAs call
    b) call the Sub account_code where you have my code in a macro of its own.

    Either option above should work but you need to do one or the other. Right now, based on the txt file the code isn't being asked to operate so it isn't.

    Either take the code out of the separate Sub and insert above the SaveAs line, or insert a Call account_code line to activate the separate macro, also above the SaveAs call.
    rsernowski's Avatar
    rsernowski Posts: 13, Reputation: 1
    New Member
     
    #5

    Feb 4, 2009, 05:15 AM
    Quote Originally Posted by JBeaucaire View Post
    I'm looking at the macro and I don't see where you:

    a) inserted the code I suggested prior to the SaveAs call
    b) call the Sub account_code where you have my code in a macro of its own.

    Either option above should work but you need to do one or the other. Right now, based on the txt file the code isn't being asked to operate so it isn't.

    Either take the code out of the separate Sub and insert above the SaveAs line, or insert a Call account_code line to activate the separate macro, also above the SaveAs call.
    Hi Jb

    Thanks so much. I created another macro called usd and called it from my initial macro. Works fine. But if I just insert the same code into the initial macro above the save as it seems to not recognize the data that is in AA21 because it types in the "else" data. Any ideas?
    Thanks
    Robert
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #6

    Feb 4, 2009, 09:24 AM

    I would open a blank sheet, type USD into cell AA21 and manually run a macro of just the code I gave. If it runs properly, then I would suspect the other sheet's data. You can post it here or shoot it to me and I'll look at it if you'd like.


    First go into your profile for the forum and put in your email address, then send me a message by clicking on my name.
    Click on my name and send
    rsernowski's Avatar
    rsernowski Posts: 13, Reputation: 1
    New Member
     
    #7

    Feb 4, 2009, 02:05 PM
    Quote Originally Posted by JBeaucaire View Post
    I would open a blank sheet, type USD into cell AA21 and manually run a macro of just the code I gave. If it runs properly, then I would suspect the other sheet's data. You can post it here or shoot it to me and I'll look at it if you'd like.


    First go into your profile for the forum and put in your email address, then send me a message by clicking on my name.
    Click on my name and send
    Hi JB,

    I sorted out my macros better, into more call commands , 3 of the 4 calls rely on cell o9 being either USD or CAD. As you said I tpye in cell o9 (that is where I move CAD or USD too) and it works, but when run the entire macro it fails on identfiying CAD. Any ideas?
    Thanks
    Robert

    Attached is the native file
    Attached is the new template
    Attached is the call command
    Attached Files
  3. File Type: xls trade1.xls (23.0 KB, 182 views)
  4. File Type: xls DAP-RAP Spreadsheet.xls (31.5 KB, 209 views)
  5. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #8

    Feb 4, 2009, 06:09 PM

    I can find no macros in either one of those uploaded sheets.

    But in the trade1.xls, I also can't find a cell with CAD in it. I did find a "MERGED" cell with Currency: CAD in it, but that's not "CAD", far from it. Merged cells play havoc with macros, I stopped using them years ago.

    Change the content of that cell to just CAD, and see if your macro (missing) will run.
    rsernowski's Avatar
    rsernowski Posts: 13, Reputation: 1
    New Member
     
    #9

    Feb 5, 2009, 05:38 AM
    Quote Originally Posted by JBeaucaire View Post
    I can find no macros in either one of those uploaded sheets.

    But in the trade1.xls, I also can't find a cell with CAD in it. I did find a "MERGED" cell with Currency: CAD in it, but that's not "CAD", far from it. Merged cells play havoc with macros, I stopped using them years ago.

    Change the content of that cell to just CAD, and see if your macro (missing) will run.
    Hi JB,
    The macro converts the merged cells into one cell , then does a text to column conversion, then copies the cell with text CAD into the TD trade ticket. You are correct. I run macro up to the point of the if commands, then I run if cell = CAD , and it fails. But if I manually type in CAD or USD it works fine. So something in the unmerge, and copy and paste into the td trade ticket does not recognize the CAD or USD . I looked closely at the cell that gets created, it is NOT CAD, it is {space} {space} CAD!! So either I call my if command using the spaces or I remove the spaces before hand. I would like to have cleaner data, how do I programmatically remove spaces?
    Regards
    Robert
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #10

    Feb 5, 2009, 02:18 PM

    Your message above outlines the REASON merged cells aren't used in production sheets. It's hoops and tricks that are completely unnecessary. You are processing "visually" designed sheets like they're data sheets and paying a stiff price in terms of your time and maintenance, aren't you?

    So reconsider this. Even if you work it out, you'll be maintaining this ad nauseum and it is SO easy to break stuff this way.

    Work with raw data sheets as raw data. Play with it, crunch it, cross-reference it, and when you're DONE, then have your "display" sheet print out the pretty stuff for the client. Apples and oranges, in my opinion.

    =======
    Speech over. Use the TRIM function to clear unwanted spaces.
    Code:
    Range("A1").Value = Trim(Range("A1").Value)
    rsernowski's Avatar
    rsernowski Posts: 13, Reputation: 1
    New Member
     
    #11

    Feb 5, 2009, 02:29 PM
    Quote Originally Posted by JBeaucaire View Post
    Your message above outlines the REASON merged cells aren't used in production sheets. It's hoops and tricks that are completely unnecessary. You are processing "visually" designed sheets like they're data sheets and paying a stiff price in terms of your time and maintenance, aren't you?

    So reconsider this. Even if you work it out, you'll be maintaining this ad nauseum and it is SO easy to break stuff this way.

    Work with raw data sheets as raw data. Play with it, crunch it, cross-reference it, and when you're DONE, then have your "display" sheet print out the pretty stuff for the client. Apples and oranges, in my opinion.

    =======
    Speech over. Use the TRIM function to clear unwanted spaces.
    Hi JB

    Good advice! They will not give me access to the raw data or provide me with a raw data ticket that is usable. The trade ticket you saw is generated from a windows terminal program running over the internet, so you know about the security risks if given access to the data. I asked them to give me a trade ticket with no formatting just raw data. They did, but it was missing all the header information! What was that ! And for some reason , when the original trade ticket, the data on the header is merged cells! I have a work around ! The trim worked! Thanks again for your help

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Multiple-step income statement in good form and retained earnings statement [ 1 Answers ]

Hi I would like someone to check to see if I did this correctly, if I did not then please help. Thanks The Problem is: The following pretax amounts pertain to River Corp. for the year ended December 31, 2008. Sales... $400,000 Operating Expenses... 84,000 Extraordinary gain... 30,000...

Income statement, owners equity statement and balance sheet [ 6 Answers ]

Les Fleurs, a boutique in Paris, France, had the following accounts in its accounting records at December 31, 20X2 (amounts in Euros, denoted as "E") Purchases………………... E250,000 Freight In……………… E8,000 Sales discounts…………. 4,000 Purchase returns…….. 7,000 Inventory Sales…………………. ...

If statement in excel macro not working correctly [ 1 Answers ]

I tried to insert the following code into an Excel macro If Range("m1") = "" Then MsgBox ("Number of Units is blank") End If I am doing something wrong, because I get the msgbox whether cell m1 is empty or not. Can anyone help me with the correct syntax?

What is the correct financial statement-statement of cash flows wording? [ 2 Answers ]

Cash (used) in operating activities <10,000> Cash (used) in investing activities 10,000 Thank you for any help you can provide.


View more questions Search