Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test for condition (Office)

    I have a spreadsheet where I need to test for certain conditions and based on the condition returned create an offsetting line. For example in the attached spreadsheet I would like to test for condition in column E (44XXXXX) and where equal to that figure insert a line below that line create an offsetting line with the conditions stated in the worksheet. Any assistance is greatly appreciated. Thanks!!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for condition (Office)

    Could you explain what you mean by "insert a line below that line create an offsetting line"? Do you want some kind of border under the cell containing that value?
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test for condition (Office)

    I'm not clear about the XXXXX: did you mean that XXXXX is to be copied to the other cells? Did you mean to have four X's in one place and 5 X's in another? See if the attached macro does what you want. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for condition (Office)

    I'm trying to create the offset of a one-sided journal entry (Accounting) where if I have a certain condition (Project = 44XXXXX) then the script does the following:

    1) Copy the information from the line where the project is found it will insert a line below.
    2) Change the unit and AT columns to fixed values
    3) Keep the values the same from the original line in the product, DD, AT, flip sign of Tot. amt and copy value, keep Alt value and descr.

    Ex.

    77 AAAAA 0919 777777 44XXXXX 833795.50 Fxxxxx Test Data

    New line when condition is met

    85 AAAAA 0919 CRXXXX 44XXXXX -833795.50 Fxxxxx Test Data


    I hope this is clearer. Thanks for your assistance!!

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test for condition (Office)

    Does this need to happen dynamically; ie when the user moves to the next line? Or, does this happen one time to the entire sheet when the user executes a macro?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for condition (Office)

    The end user will have a sheet with anywhere from 200 to 500 lines all differentiated by various projects. I need the script to be flexible so that it can test for various conditions in a loop. For example if it sees 44XXXXX it will provide a corresponding offset that will be the same other than the amount everytime it comes across a line that has the same project. If it passes another project than the script should be able to provide another offset. I can think this thru very clearly but I don't know scripting well enough to code it. By the way your script worked perfectly but I need the script to run against the entire wksheet as opposed to one project at a time.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test for condition (Office)

    Here is something to get you started. My goal was to create a table of project numbers, then let the user indicate what corrections (if any) to make to each project, and finally to created a corrected journal sheet based on that table. There are two macros: GetProjList and CorrectSheet.

    First the user runs the GetProjList macro which creates a new worksheet with a project table at the top and instructions at the bottom. The user makes entries in this project table:
    a minus sign means negate original entry; or
    any number means add it to original entry; or
    any text means replace original entry; or
    a blank means use original entry.

    Next, the user runs the CorrectSheet macro which copies the original worksheet and applies the changes.

    If you need more, you will have to teach me some accounting; ie what is a one-sided journal entry, and explain more slowly with a before & after sample of what you want. I could use the accounting instruction: I'm a treasurer for a volunteer orgination. Most of my funds are send directly to headquarters, but to one of four different funds; in addition, I have a local fund to pay for local expenses, but only one checking account; and finally some income is cash and some is credit card. Way too many interconnected balances! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Have fun! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for condition (Office)

    Sam,

    I want to thank you for your considerable efforts on my behalf. I should have provided you more information based on the questions that you had yesterday. I've attached my working script so you can see my current process. It takes a body of data and based on information populated in the Proj. column, creates a JE that has one side of the entry (hence one sided). What I need to create is a continuation of this script that based on project will offset the total activity for each project to a specific combination. I feel like I know enough to be dangerous when it comes to VBA. I can visualize what I want to accomplish but putting it into working code leaves a lot to be desired.

    I looked at part of the recent code in the CorrectSheet procedure. I believe that all that I would need is something like the Select Case method where once the output is sent to various sheets by project the script would loop thru the various sheets and based on the project found on the sheet it would create an offset which would be the same one used for all lines on that respective project sheet. At the end of the routine you should have "X" amount of worksheets where you have a Journal Entry that should net to zero. You should have the original amount from Sheet 1 with a line inserted right below it that offsets that amount to 85 and the remaining fields that I included in the attachment. Thanks again for your efforts.

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test for condition (Office)

    I'm sorry, but NONE of that makes any sense. Please slow down and provide lots of details. Also show what the workbook looks like before (with fake data, of course) and what you want the workbook to look like afterwards. Then, give me the steps that you would have to do manually (without code) to produce the final produce. Don't leave out any steps, no matter how obvious they seem. I believe that you have a problem that can easily be solved with a macro, but I do not want to provide another wrong solution.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for condition (Office)

    Sam,

    I'm sorry that what I provided wasn't clear. I will try again. I've attached a wksheet that has a macro button presently attached to a script called CreateJE. This script collects information on Sheet1 and creates a sheet (with corresponding sheet name) for every change in project (Column A). Each line on these new worksheets represent one side of a JE that my client would like to record. In order to complete the entry I would need a script that then goes after the data in the newly created worksheets and create an offsetting line so that the entry nets to zero by product/DD/project/Alt. Please see the sheet labled Desired Output for an example of what the output should look like. Hopefully, this is clearer. Thank you for your patience!!

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test for condition (Office)

    No, it isn't clear at all:
    Is the DesiredOutput sheet a new sheet or a mod of Sheet1 or just an indication of what the nnXXXXX sheets look like?
    In the DesiredOutput sheet:...
    where does the info in column A come from?
    where does the info in column DD come from?
    why doesn't the data in row 11... match the data in sheets 44XXXXX...?
    What if there are two lines in the original for project 44XXXXX?
    I would expect to see formulas (for running balances, etc) in the original. Are there formulas? What do I do with them?
    Don't you want totals or some sort of math in the nnXXXXX sheets?
    Does the macro create the nnXXXXX sheets? What if they already exist?
    Is there always just one line on each of the nnXXXXX sheets?
    Should the nnXXXXX sheets be created in any sort of order and in any particular place in the workbook?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for condition (Office)

    Is the DesiredOutput sheet a new sheet or a mod of Sheet1 or just an indication of what the nnXXXXX sheets look like?

    The DesiredOutput sheet is just that. This is what I would like the output to look like but on the respective sheets that are created via the Macro. If you delete all sheets other than Sheet 1 or DesiredOutput and run the macro you will see what the current process provides.

    What if there are two lines in the original for project 44XXXXX?

    All lines that share the same project number would be picked up by my current script and copied to the newly created sheet by project.

    I would expect to see formulas (for running balances, etc) in the original. Are there formulas? What do I do with them?

    No formulas. The template is a data dump of information that is used to create a journal entry.

    Don't you want totals or some sort of math in the nnXXXXX sheets? No!!

    Does the macro create the nnXXXXX sheets?

    Yes, please hit the button to see how the macro works. Delete all sheets except for Sheet1 and DesiredOutcome sheets. All remaining sheets are created via the existing macro.

    What if they already exist?

    Answered above!!

    Is there always just one line on each of the nnXXXXX sheets?

    Whatever is in Sheet1 will be pulled over by project (information will be place on a project specific sheet i.e. 44XXXXX lines will be on a sheet labeled 44XXXX). If there are 20 lines of data that have project 44XXXX, then the script will copy 20 lines to that sheet. Again, if the script is initiated this should make more sense.

    Should the nnXXXXX sheets be created in any sort of order and in any particular place in the workbook?

    This part of the process is already handled by the current routine. I just need assistance to create offsets for the resulting information that is created on the individual sheets that are created by my current script. On my DesiredOutput sheet I state what I would like the format to look like on there but the only difference would be that the offsets would be on the individual project sheets (Ex. 44XXXX sheet should have offsets that net all the lines that are created by my current script to zero). Run my script and then read this answer again as hopefully it should be more evident as to the point I'm trying to get across.

    Again, please delete all sheets (except Sheet1, and the DesiredOutput sheet) in my file that I attached earlier, push the macro button (column J) on Sheet 1 which will kick off my current script. You should have sheets created for every instance where there was a different project number on Sheet1. What I need from that point is a routine that will pick up where my current script leaves off, goes into each project specific spreadsheet, create an offset based on my example in the DesiredOutput sheet.

    Example: (This offset for project 44XXXX would be on placed on the sheet labeled 44XXXX). I think you could sum up the total activity on each project sheet and make one line to offset the all amounts per project sheet or you could create an offsetting line for each line created by my script. Let me know your thoughts as to which would be easier..

    77 AAAAA 0919 777777 44XXXXX 833,795.50 FXXXX Test Data
    85 AAAAA 0919 CF29XX 44XXXXX -833,795.50 FXXXX Test Data

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test for condition (Office)

    Much clearer, you only skipped three questions:
    <hr>
    In the DesiredOutput sheet:...
    where does the info in column A come from?
    where does the info in column DD come from?
    why doesn't the data in row 11... match the data in sheets 44XXXXX...?
    <hr>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test for condition (Office)

    Well, I've run your macro and I have one, no, two, no, three more questions:

    Do you really want to destroy the original worksheet by the pastespecial-multiply and the sort? Seems like that makes it tough for the auditor?

    Is the pastespecial-multiply the way you want to change signs? Will it always be a sign change or will you want to do a multiplication sometimes?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Test for condition (Office)

    I just noticed that your macro also changes the order of the columns. Do you want to do that?

    BTW, is your name John? I had a boss once whose name was John. Nearly drove me crazy. Never gave me any information until I had done the project wrong. I just realized that you did not follow my instructions:
    <hr>
    show what the workbook looks like before (with fake data, of course) and what you want the workbook to look like afterwards. Then, give me the steps that you would have to do <font color=red>manually</font color=red> (without code) to produce the final produce. Don't leave out any steps, no matter how obvious they seem.
    <hr>
    Can you do that, please? This is not just the ranting of an old man: it will help you solve the problem. Remember SammyB
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •