Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Dynamically expanding template, cell numbering & turning formula to value after running.

    Hi everyone,

    I have 3 thing I would like to do and been struggling to figure out on my own. (Please see the attached file for exact document)

    - Each box represent a trade template. There are 4 trades in 1 row and they keep repeating. I would like to create a dynamic way to expand them based on the last date entered. When an actual date is entered into the cell called "Date", it should trigger a macro to check if there is at least 2 rows of templates left, drawn in advance, if not, copy and past the new row of 4 templates.

    - In the cell "B2" I have a counter, this repeats 3 more times every 7th cell and keeps on happening every 24 rows but only when a new row of trade templates are inserted. I was hoping someone more experienced could show me a more elegant way to do this, that would work from start and would not require a different formula for the first item of each row.

    - Lastly the content of D10,E10,D17 & E17 if based on D4. The problem I am having is that D4 can decrease as sales start to happen, but the quantity in Cost and Return should stay constant. Theoretically I am looking to turn the formula in these cells into value after the content of the cell changed once - for the first time.

    Trades.xlsm

    Thank you very much for taking the time to consider this request.


    PS: Not sure if this is allowed or not, but if it is, I would like to offer a 20$ pizza reward - via PayPal - to the first person who can complete these 3 goals via VBA, except where/if formula makes more sense.
    Last edited by Ferenc Nagy; 2015-06-19 at 06:19.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    In the example file attached:
    1. When you enter a date in any of the four Trades in the last block at the bottom, then a new block of four empty Trades will automatically appear below.

    2. I changed the formula in [B2].

    3. When you enter a value for the first time, the corresponding entry for Cost and Return is set to this value automatically. If you then subsequently change a value, any existing value for Cost and Return is left unchanged.

    How this is done:
    >1. A 'hidden rows section' of empty block of trades (at the bottom) is used for creating a new set of trades.
    >3. All formulas in cells used for Cost and Return, have been replaced with a question mark character. When a value is first entered, a test is made to see if the corresponding Cost cell contains a ?
    If it does, then both the Cost and Return cells are replaced with that value entered.

    Your pizza offer is a treat. Please find a deserving hungry person near you.

    zeddy

    PS: I adjusted some column widths to allow date entries to be seen, during testing. And made the column widths 'whole numbers' because I like whole numbers.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hey Zeddy,

    Thanks for a quick reply.

    - I see there is no magic formula for the counting/numbering of the templates, since you left the 2nd, 3rd and 4th in each row same as before but at least now the very first number is established by a formula.
    - The Pasting of the new row of templates seems to work well.

    - When it comes to changing formula to value, it works for the first row but not for the 2nd, 3rd, 4th and 5th. Since there can be a maximum of 5 items in a trade template, it would be ideal to have the same function for the rest of the rows as well. This might be the result of lacking proper explanation on my part. While there is a ? mark in the first row for the benefit of giving some idea what goes where, this is not the case in the 2nd, 3rd, 4th and 5th rows of the template. However the 2nd, 3rd, 4th or even 5th item can sometimes be entered and the same problem happens, once sales start to happen the Cost and Return values change as well when quantity is lowered. Can the VBA be modified to do the same check for each row at the time of first change in "D4 : D8" and turn formula to value in "D10 : D14" and "D17 : D21" accordingly but row by row each time?

    PS: I like whole numbers as well
    Last edited by Ferenc Nagy; 2015-06-19 at 15:03.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    ..aha! that wasn't clear in your example.
    I'll post an update tomorrow.

    A 'magic' formula could be given if you really really want one.

    zeddy

  5. #5
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Ferenc

    ..aha! that wasn't clear in your example.
    I'll post an update tomorrow.

    A 'magic' formula could be given if you really really want one.

    zeddy
    Thanks for that!

    'magic' formula is more about trying to learn, I know it can work without it...

    ...and I found use for your pizza money: http://i.imgur.com/cYhn4d4.jpg

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    In version2 attached:
    I added a 'magic' formula for the Trade Template item number.
    The formula is
    =itemNumber()
    ..and it is the SAME formula in all the relevant cells.
    I don't think you could get a simpler formula.

    ..and this is a little simpler and easier on the eye than my previous
    =(INT(ROW(A2)/24)*4)+1

    I also simplified the formulas in the Cost and Return sections of each Trade Template..
    e.g. replace
    [B10]=IF(B4=0,"",B4)
    with
    [B10]=""&B4
    and replace
    [B17]=IF(B4=0,"",B4)
    with
    =B10

    etc etc etc

    I amended the VBA code to deal with quantities for 2nd, 3rd, 4th and 5th.
    You should review the code to see how it was done. Training training training.

    Note:
    The function you are using for calculating profits will stop counting as soon as it encounters a 'blank' date as it goes across each of the four Trade templates in a block.
    That means it will not include any trades in rows below, that have dates in them.
    This is OK if you always make sure you enter all four trades across, before starting in a new section below.

    zeddy
    PS nice donation!
    PPS I liked the view from Winter Hill. Haven't been there for years and years
    Attached Files Attached Files
    Last edited by zeddy; 2015-06-20 at 07:18.

  7. #7
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thank you for the improvements Zeddy. I used the simplifications in several other places in the "GmailCurrent" tab... Best way to learn it is to use it

    I wanted to use the VBA you created for the quantity change in "Trades" tabs but after looking at it for a while, I couldn't figure out how to change it to do the same horizontally In "GmailCurrent". I wanted column "I" to pick up the value of "B" when change occurs for the first time in "B" but have the feeling doing 45 cases for this might not be the most efficient way. Could use your help...

    I managed to find a way to automatically have today's date entered when a new sellers name is entered in column "E", so that is a small victory.

    I'm sure you will recognize the VBA between "GmailCurrent" and "GmailPast". I did some minor modification to it, mostly just the area of work as I changed the look of the tab. I encountered 1 previously unforeseen problem with the VBA function. Whenever the quantity is lowered to zero and the row is moved to "GmailPast", the entire row is cleared, including any formula present. Could the Clear command be modified to only clear cells B to G, L & R in the selected row? That would eliminate this problem all together...

    Thanks
    Ferenc

    Trades 2.0.xlsm

    Note: The Monthly and Total sale counter should end at first blank date because it means that trade did not happened yet. It is continuous, so this functions as intended.
    Last edited by Ferenc Nagy; 2015-06-21 at 07:57.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    In "GmailCurrent". I wanted column "I" to pick up the value of "B" when change occurs for the first time in "B"
    ..Do you mean you want Cost Qty in column [J] to pick up initial entry in Qty column [C] ???
    ..if so, then see updated code in attached file.

    I managed to find a way to automatically have today's date entered when a new sellers name is entered in column "E"
    ..well done. In the VBA code you used Now (which puts date AND timestamp) rather than Date (which just puts in the current date, as used elsewhere in the code). Including the timestamp may be useful.
    See my code for another way of dealing with this. With your method, if you re-typed or amended the Seller's name (e.g to fix a mis-spelling) at any later date, this would cause the Buy Date to be updated. My method puts the Buy Date in only if there isn't one there already.

    Could the Clear command be modified to only clear cells B to G, L & R in the selected row?
    ..Yes. see updated code.
    I assume you also wanted to clear J???
    see code to see how this is done.

    Please test the update, review the vba code, and let me know if this works OK.

    zeddy
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    ..Do you mean you want Cost Qty in column [J] to pick up initial entry in Qty column [C] ???
    ..if so, then see updated code in attached file.

    - That was exactly what I meant to do. After some experimenting I realized that both "I" and "O" needed to use the same formula to allow for change without one column affecting the other column. Now they both depend on "B" independently.

    ..well done. In the VBA code you used Now (which puts date AND timestamp) rather than Date (which just puts in the current date, as used elsewhere in the code). Including the timestamp may be useful.
    See my code for another way of dealing with this. With your method, if you re-typed or amended the Seller's name (e.g to fix a mis-spelling) at any later date, this would cause the Buy Date to be updated. My method puts the Buy Date in only if there isn't one there already.
    - Your method of handling Date information fixed a problem I did not even discover yet, until you mentioned it. Thank you for that

    ..Yes. see updated code.
    I assume you also wanted to clear J???
    see code to see how this is done.
    - You were correct, I meant to clear "J", "L", "P" & "R" as well. Especially now that "J" and "P" is being populated automatically.

    - In "Trades" I applied a conditional formatting to the "Seller" cell. The result of my formula "=SUM(D48)<1" is correct every time, yet the resulting formatting is not consistent with the formula results. I entered sample trades in the first row that creates all 4 possibilities. The only one that should result in sellers name being written with black is when all items sold. Contrary to this, this is also happening when the first item was sold but the second item isn't yet. I could use your insight on this, as I don't see logical explanation for the behavior, unless something in the VBA interferes somehow. As I said, clueless

    Trades 2.1.xlsm

    This last one only looking for your opinion and/or advice, don't want to abuse your help. I would like to know if it makes sense to you and if I should explain it or write it differently. I will post it in a new thread once it is coherent.
    - For the first part I been trying to come up with a logical solution where the trades could be automatically copied over to "GmailCurrent". I realized there is no certain act that could be used as a trigger, because there is no way to tell when the last trade item is entered, so I think this might be something to be initiated by a shortcut and it should only work for the last trade entered to avoid duplicates, unless there is a check for that but that just seems unnecessary complication of the VBA code. I was thinking find last "live" Date and work with that trade for the function of copying cells to "GmailCurrent". After taking some notes and refining the 2 tabs to match in many aspects, I came up with the following from-to examples:

    Example 1:
    Trades --> GmailCurrent

    • B4 --> B47
    • D4 --> C47
    • F4 --> D47
    • B2 --> E47
    • F10 --> L47
    • F17 --> R47


    Example 2:
    Trades --> GmailCurrent

    • B5 -->B47
    • D5 --> C47
    • F5 --> D47
    • B2 --> E47
    • F11 --> L47
    • F18 --> R47


    Example 3:
    Trades --> GmailCurrent

    • W4 -->B47
    • Y4 --> C47
    • AA4 --> D47
    • W2 --> E47
    • AA10 --> L47
    • AA17 --> R47


    I thought if the target row would be the last row of GmailCurrent - 47 - then the triggered alphabetical shorting after the copying would take care of the positioning of the newly entered row. Should the sheet ever be full, which is very unlikely, then I would just adjust the sheet length and set the last row number to whatever in the future it will be.

    The second part would be about updating quantity on "Trades" tab if Quantity is changed in "GmailCurrent" "C" column. This is the logic I got so far:

    Example:
    GmailCurrent --> Trades
    • When change happens in "GmailCurrent" column "C" use the "Trade #" in "E" to locate the trade in "Trade" tab
    • Search for exact match of "GmailCurrent" "B" in "Trade" "(itemNumber.column, itemNumber.row +2) : (itemNumber.column, itemNumber.row +6)" (This one is messy, didn't know how to write it better)
    • When item found, update the quantity of item in "Trade" in (itemNumber.column + 1)


    For this to skip problems of same seller in more then 1 trade, I changed the "GmailCurrent" "E" from "Seller" to "Trade number", this way the item has a unique identifier every time.

    As you can see that last past is especially in need of better wording...
    I would appreciate any thoughts on it.

    Ferenc

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    ..I will have a look at this, but I'm working on other stuff too, so please be patient.

    zeddy

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    ..I'm still looking at this.
    ..In your file Trades 2.1.xlsm the conditional formatting formulas need fixing on sheet [Trades].
    You need to include the =SUM in the conditional format formulas.

    The transfer of data from the [Trades] sheet to the [GmailCurrent] sheet could be done via a custom right-click menu.
    This vba process would also include appropriate sorting.

    When changing qty values on the [GmailCurrent] sheet , the corresponding values on the [Trades] sheet can be synchronised via vba.

    zeddy

  12. #12
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    ..In your file Trades 2.1.xlsm the conditional formatting formulas need fixing on sheet [Trades].
    You need to include the =SUM in the conditional format formulas.
    So the code on [Trades] was interfering with the conditional formatting of the cell used for seller names? As in it was not executed because it isn't in the vba on the sheet?

    The transfer of data from the [Trades] sheet to the [GmailCurrent] sheet could be done via a custom right-click menu.
    This vba process would also include appropriate sorting.
    Was considering a button solution but this would eliminate the problem of having to scroll to the button...

    When changing qty values on the [GmailCurrent] sheet , the corresponding values on the [Trades] sheet can be synchronized via vba.
    That's what I thought, but you have better wording

    I been working on some vba so I should have significant improvements done today. As I mentioned before, just looking for your opinion about the logic for the request and maybe a bit better wording, but don't worry about it too much. Anything you have time for I am grateful for!

Posting Permissions

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