LinkBack URL
About LinkBacks
Hi,
Looking for VBA code that could automatically generate the data as highlighted with yellow. i.e from I5 to S17 could be increased at right and below as data gets increased.
Plz find in attachment file.
Hi,
Looking for VBA code that could automatically generate the data as highlighted with yellow. i.e from I5 to S17 could be increased at right and below as data gets increased.
Plz find in attachment file.
HI
Why VBA? Wouldn't Conditional Formatting for the desired range suffice if the cell is not empty?
Look at your attachment with the FC.
HI,
Mario
You didn't understand what I mean to say.Refer see my attachment now.
Hi
Sorry, i don't understand
Hi,
Mario
Deleted.............
Last edited by jindon; 05-22-2023 at 12:38 AM.
There was missing data in O3 which will be 2 has now been updated in below attached file.
I hope vba experts will help.
After a long search.I am able to get below code.It gives correct result prompting with an error only in a condition when A:D are in value format and not in formula format.
Can vba experts,help in changing below code so that it can works in formula format in A:D.
Please Login or Register to view this content.
Last edited by Etax; 05-24-2023 at 02:59 AM.
Hi, Experts,Is there possibilities that above code can be converted to work in formula based format or not.
It seems that I understand what you want. Take Column I as an example. The quantity is 50 and you have 10, 15, and 17 satisfying
criteria 1 and 2. However, the sum of these 3 numbers is still short of 8. Hence, you want write 8, 10, 15, and 17 in Column I.
Am I right?
Your column S is still not correct. Or maybe I am wrong. If the quantity is less than whatever available, just fill that number.
Are A3:D4 always empty?
Last edited by Torontohrb; 05-24-2023 at 05:15 PM.
My understanding is you want to write numbers in those highlighted cells. If your raw data always start from cell A5, the following code should work.
I have no incentive to write VBA code any more. I wrote it using Office Scripts, which I think it is easier than VBA and it will replace VBA soon. It is not too complicated, but tedious.
If you use Office 365, click Automate tab, then click 'New Script' and copy the following code and run. Please let me know if it works on your computer.
In case you want to add a column in Column T, simply change 6 in the code to 7 (two places).
Please Login or Register to view this content.
Last edited by Torontohrb; 05-24-2023 at 08:20 PM.
No,I am using office. 2021.No,Office scripts doesn't seems to work in my version of excel.
Last edited by Etax; 05-24-2023 at 08:57 PM.
Originally Posted by Torontohrb
Here ,8=50-(10+15+17) i.e [I4-Sum(I6:I8)]
Column S is correct,becoz both criteria matches,hence from D11 which is 20,it takes 10 only as S4 in S11.But if S4 was 25 after satisfying both criteria,the value would be S11=20 and S10=5(taking 5 only from D10 which is holding value 75.whose calculation is same as S10=S4-Sum(S11:S11) i.e 25=25-(10).
A3:D4 are coming due to formula.This part also I would like to trim and remove as well.Plz refer in my above file there is formula in A3.Necessary changes can be done at your end by changing the formula to achieve the result.However ,it looks from A5 excluding heading.
I hope,I have given reply to all the queries.
Last edited by Etax; 05-24-2023 at 09:23 PM.
To all the experts> Interpretation,
The sum of each column from row 5 to 17 should equal the value in row 4. Only use values from column D. Start with the last row where the product and month in column A & B match the product and month in row 2 & 3. Then continue using the values from previous rows until the total would exceed row 4.
If the next value in column D would cause the total to exceed row 4, just enter the value needed to equal row 4.
Example Column I:
Target value is 50
Last matching row is row 8.
Row 8 value is 17, total is 17, enter 17 in I8
Row 7 value is 15, total is (17 + 15 = 32), enter 15 in I7
Row 6 value is 10, total is (17 + 15 + 10 = 42), enter 10 in I6
Row 5 value is 10, total would be (17 + 15 + 10 + 10 = 52) which exceeds the target. The value needed to meet the target is (50 - 42 = 8). Enter 8 in I5.
# Post 7: formula interpretation:
Source:from Internet.
I think this will help the experts to resolve this issue.Please Login or Register to view this content.
Last edited by Etax; 05-25-2023 at 03:49 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks