Posts by KjBox

Your welcome.

Note that if you go with the divider Column method the that column can be hidden in the data. You would need to add a bit of code right at the end to hide it again in the Changes sheet.

Also delete the "On Error Resume Next" that you added.

Here is the code with comments added, I hope it will help you follow and understand what each part does!

All you need do is add 1 to the value of i

Note I changed UsedRange.Rows to UsedRange.Columns.

That was an error by me originally, .Rows just happened to work with the sample file but might not with your actual file.

I do not know why the code is not picking up the currency formatting for you, possibly because a custom format is being used.

Try this version. It should work.

All you will need to do with you actual file is ensure that all columns up to the first column that need monitoring are formatted as "General"

It was overwriting because of the protection of the new sheets, my fault, when the code adds a new sheet it gets protected, but I forgot to include UserInterfaceOnly = True. That is now fixed.

I am attaching a workbook where I added 3 new sheets and added data to each. The Site Totals Table updates correctly, but, it does throw up a problem.

You will see that I added data for 2 different Rooms for Science Block Level G, they have different Difficulty Factors (1 & 3). Since there is just 1 "Difficulty" entry for each row of the Site Totals Table, the code only records the last entered, or any data update, Difficulty Factor (in this case 3 for tiling the Chemistry Lab). Not sure of the best way to overcome this issue, maybe an average of all entered Difficulty Factors for the same Block/Area & Level? Alternatively a new row for every room on each level?

I have also added code that will sort the Site Totals Table by the first column, so all Block/Areas are grouped no matter when they are entered. Also code to sort and group the Sheet Tabs.

Here is an illustration of why Excel Built-in Tables (called ListObjects by VBA) are easier to work with. This code snippet

Checks if the Block/Area & Level are already in the Table, if not then a new Table Row is added (only if the Table already contains some data, if not the the first Table Row is used) and the data placed in that new Table Row, otherwise the correct Table Row gets identified and the data in that row is updated. No need to check for "the next empty row", also any formulas in the Table are automatically copied down to the newly added row.

I am attaching 2 files, one empty with the modified code, the other with 3 extra sheets with data added so you can see the issue with Difficulty Factor.

You have not noticed the parts of the formula that are in parentheses!

ReDim z(1 To UBound(x, 1) - 1, 1 To UBound(x, 2) * 4 - ((lCol - 1) * 3))

Ubound(x,1) = 20, UBound(x,2) = 17 and lCol (which is the Column where monitoring needs to start) = 8

so

z(1 To 8 - 1, 1 To 17 * 4 - ((8 - 1) * 3)) equates to z(1 to 7, 1 to 68 - (7 * 3)) which, in turn, = z(1 To 7, 1 To 68 - 21) = z( 1 To 7, 1 To 47)

Hdrs is the ReDimmed 1 to UBound(z, 2), which is 1 to 47

Rename the file you are testing with and send it to me I can then see if something weird happened when Uploading and/or Downloading.

You are getting the error when using the version 1.6 file I attached? It runs perfectly for me.

If the error occurs when you copy the code to your working file then I need to see that working file.

Please put a post in ExcelForum and MrExcel to say that the problem was solved at Ozgrid. Then people will not waste their time working on a solution for you.

Thanks.

You're welcome

Sorry code line 19 not 17

In the code line 17 is If x(ii, 2) = "Buy" Then , try changing "Buy" to "خرید" but still within the quotation marks

Again, not certain.

I think you should try changing the sheet text to Persian and leaving the code variables in English.

If that works then try changing the code too.

Not absolutely certain about that!

I suggest you make a copy of the file first, then change that to Persian script.

The attached file has the format for Inventory changed to include thousand separator.

Thanks for posting links to cross-posting.

Did you see my solution in Post#2?

Try the attached.

Click the button on the Transaction sheet.

Code assigned to the button is:

Note:

You had an error in your sample file, the average Buy for Share 1 should be 7,752 not 7,749

Any Short Sell will have the Inventory in Red text, I changed Share7 Sell to 4,000 to illustrate that.

Here is the file, the code will find the correct starting column dynamically.

ok thanks, that is great, I can do the code so that it will automatically get the correct starting column, no need for user input.

OK, will the first column that needs monitoring always be formatted as Currency as it is in the image you showed?

I was thinking of much the same thing. Are all the columns (excluding Column A) that do not need to have changes monitored contain only text, and the columns that do need monitoring contain only numerical values?

You're welcome