Check out some similar questions!

rsernowski · Feb 2, 2009, 12:25 PM

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 · 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 · Feb 3, 2009, 05:53 AM

Originally Posted by JBeaucaire

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

JBeaucaire · 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 · Feb 4, 2009, 05:15 AM

Originally Posted by JBeaucaire

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 · 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 · Feb 4, 2009, 02:05 PM

Originally Posted by JBeaucaire

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

JBeaucaire · 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 · Feb 5, 2009, 05:38 AM

Originally Posted by JBeaucaire

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 · 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 · Feb 5, 2009, 02:29 PM

Originally Posted by JBeaucaire

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