[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

Part 4 - Copy only hardcoded values to the correct row and column of a another spreadsheet

Currently for the cells that have formula’s you are not checking to see if the Updated Forecast file already has that value like you are for the cells that do not have formula’s.  What I’m seeing is some weird counting issues.  For example, let’s say you hardcode 1 record and that record is a cell that has a formula.  After you click the “Update This Months Forecast”  button it will say “A total of 1 cell(s) were updated…”  If you were to click the “Update This Months Forecast”  button again without making any other changes, it will continue to say “A total of 01 cell(s) were updated…” even though that value has already been updated and no changes were made.  I would expect it to say “A total of 0 cell(s) were updated…” if that cell has already been updated.  However, if you update a cell that does not have a formula, you get a different behavior.  In this case, the first time you click the “Update This Months Forecast”  button it will say “A total of 1 cell(s) were updated…”  If you click the “Update This Months Forecast” button again without making any other changes it will say “A total of 0 cell(s) were updated…” which is what I would expect and I hope we can do the same thing for the cells with formulas.
0
ScottBarnes
Asked:
ScottBarnes
  • 7
  • 6
1 Solution
 
gowflowCommented:

After you click the “Update This Months Forecast”  button it will say “A total of 1 cell(s) were updated…”  If you were to click the “Update This Months Forecast”  button again without making any other changes, it will continue to say “A total of 01 cell(s) were updated…” even though that value has already been updated and no changes were made.

YES for sure as we did not save the file yet after the first run of the macro. remember the macro does not save until you press save and exit and it will then prompt you to save the workbook and it is only after that, if you run it again on the same file that it will say 0 !!!!

We cannot save the file without the consent of the user or then we will be messing with the data and possibly saving data the the user don't want to save !!!

gowflow
0
 
ScottBarnesAuthor Commented:
I understand we are not saving the file yet, however, counting behavior is different for cells that are formula's vs. values.

If you click the update button after putting a value in a formula cell it will always say 1 change was made no matter how many times you click the update button.

You get a different behavior on a non-formula cell:

If you click the update button after putting a value in a non-formula cell it will say 1 update the first time you click it and 0 each additional time you click it.

In both cases, no values have been saved but I like the way it works on the non-formula cells.  

Solution:  For formula cells, if the user hardcoded a value, make sure the updated file doesn't already have the hardcoded value, if it already has the hardcoded value becuase you clicked the update button, there is no need to copy it over again.
0
 
gowflowCommented:
PLEASE Do me a favor !!!!

Let s not run a bazar here. PLEASE sake good order post 1 question at a time. and reply to the first one first then the rest.

I will wait till we finish Part3 then when close will move to the next.
gowflow
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gowflowCommented:

Solution:  For formula cells, if the user hardcoded a value, make sure the updated file doesn't already have the hardcoded value, if it already has the hardcoded value becuase you clicked the update button, there is no need to copy it over again.

Are you sure this is the solution ???

Shouldn't it be that instaed ??

Solution:  For formula cells, if the user hardcoded a value, then only update the value if the hardcoded value is different from the value in the Updated file (same behavior as values). As your logic maybe correct time being but you may possibly run into situations where users have updated part of the file then saved the workbook and then the next day update the other part !!!

What do you think ?
gowflow
0
 
ScottBarnesAuthor Commented:
Yes, I really need this:

Solution:  For formula cells, if the user hardcoded a value, make sure the updated file doesn't already have the hardcoded value, if it already has the hardcoded value becuase you clicked the update button, there is no need to copy it over again.

For cells with fomula's, the reason I only copy it if it is hardcoded is because when the template refreshes the next month the numbers in the formula cells will be different for almost every cell.
0
 
gowflowCommented:
ok then here it is.

Check it well and let me know.
gowflow
UpdatePrevMonthForcastV08.xlsm
0
 
ScottBarnesAuthor Commented:
Very close, here is the behavior I'm looking for when I test:

1.  Works Perfect - I hardcode 1,000 for a formula cell.  I click the update macro button and it updates the file to 1,000 and brings up the dialog box “A total of 1 cell(s) were updated…”  Perfect

2.  Works Perfect - I click the update button again without making any changes and it says “A total of 0 cell(s) were updated…”  Perfect because the update file already has 1,000 in there so no need to update it again.

3.  Doesn't have the behavior I want - If I change the updated file to 1,0001 and click the update macro it should say “A total of 1 cell(s) were updated…” because now the hardcoded value 1,000 is in the original file is different than the value of 1,001 in the update file so it will need to change the value to 1,000.
0
 
gowflowCommented:
ok check this one.
gowflow
UpdatePrevMonthForcastV09.xlsm
0
 
ScottBarnesAuthor Commented:
The new code works great unless there is an item that move rows, then the following is occurring.

Col H thru S:  Works perfect even if the items moves rows

Col T:  Does not work correctly if an item moves rows.  If you enter a value in this cell and click the update Macro button, it will say “A total of 2 cell(s) were updated…” each time you click it.

Col U thru AF:  Works perfect even if the items moves rows

Col AG thru AR:  Does not work correctly if an item moves rows.  If you enter a value in one of these cells and click the update Macro button, it will say “A total of 2 cell(s) were updated…” each time you click it.

Col BL:  Does not work correctly if an item moves rows.  If you enter a value in this cell and click the update Macro button, it will say “A total of 2 cell(s) were updated…” each time you click it.
0
 
gowflowCommented:
Not sure what will do it but test this version ONLY ON COL T and let me know. If I find out the correct setting then will apply for the rest.
gowflow
UpdatePrevMonthForcastV09.xlsm
0
 
ScottBarnesAuthor Commented:
Yes, that fixed Col T even when the row that the item was on changed.  Can you replicate for:

Col AG thru AR

and

Col BL
0
 
gowflowCommented:
ok here it is
gowflow
UpdatePrevMonthForcastV09.xlsm
0
 
ScottBarnesAuthor Commented:
Works Perfect.  Thank you for all of your help.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now