# Vba required for some actions on 1 Worksheet



## hsandeep (Dec 25, 2022)

Writing vba for this is not possible …so I have approached this forum.
Actions to be performed by code:

Fill MG1 with value of $MD$4.
Then *MOVE* 5 columns on the Right Hand Side from the last column (here MG is the last column), fill the next (6th ) column i.e. MM1 with a value as =MG1+25
Repeat step 2 till it reaches column whose value <=MD147
So till here the code has filled MG1; MM1; MS1 & MY1 (4 cells provided the sample data in MD2:MD147 is as shown in the file).
The code should fill maximum ‘*header*’ 20 cells as per step 2 i.e. maximum up to QQ1 (*last header*).
Now, below each of such ‘*headers*’ MG1; there is a range MG147:MU156. In this range MG147:MU156, MH147:MH156 gets value (each cell of MH147:MH156 gets updated from another cells based on real time feeds). MG147:MG156 HasFormula of [R1C1] type like in MG147=MH147 up to MG156=MH156. MU147:MU156 by default contains “” (blank).
IF MG1>=$MD$2, then code should *remove the formulas* from MG147:MG156 and *should fill* 20 in MJ147:MJ156.
Repeat Step 7 till MY1 (i.e. up to 4 ‘*headers*’) with analogous *removal of the formulas* from MY147:MY156 and *filling of* 20 in NB147:NB156.
Now, below actions are the ‘toughest part’ of the code’s action.
IF the value of $MD$4 changes and gets *LOWERED* (it will never get increased) by 25 or 50 or 75 or in any multiples of 25 (due to lowering in the value of $MD$5), then the *NEW LOWERED* value of $MD$4 should be calculated to arrive at by how much value the lowering has taken place in $MD$4.
IF it has been *LOWERED* by 50 (for example) i.e. *2* times of 25, then naturally MG1 will get this new value of $MD$4; the code *should move* all values of ‘*headers*’ cells *RIGHT* i.e. values of MG147:MG156 *should move* to MS147:MS156 *AND* values of MJ147:MJ156 *should move* to MV147:MV156. Similarly, values of MM147:MM156 *should move *to MY147:MY156 *AND* values of MP147:MP156 *should move* to NB147:NB156 & so on till the *4th* *header* cell MY1 i.e. values of MY147:MY156 *should move *to NK147:NK156 *AND* values of NB147:NB156 *should move* to NN147:NN156.
Lastly, if $ME$1>=(now), then *PUT THE FORMULAS BACK* in all the ‘*header’s*’ column i.e. for *header* cell MG1, MG147:MG156 *SHOULD CONTAIN FORMULA* =MH147:=MH156; *AND* values of MJ147:MJ156 *SHOULD BE FILLED WITH “” (BLANK)* till the *last header* cell QQ1
Book105.xlsxMDMEMFMGMHMIMJMKMLMMMNMOMPMQMRMSMTMUMV125/12/2022  1:22:00 PM41575416004162524162734415755415981464164914741650536.00536.0020470.00470.0020555.00555.0020148326.00326.0020405.00405.0020666.00666.0020149456.00456.0020357.60357.6020777.00777.0020150256.00256.0020295.00295.0020295.00295.0020151230.00230.0020248.35248.3520248.35248.3520152203.00203.0020203.00203.0020203.00203.0020153169.25169.2520169.25169.2520169.25169.2520154137.50137.5020137.50137.5020137.50137.5020155116.00116.0020116.00116.0020116.00116.002015628.4028.402028.4028.402028.4028.4020PCell FormulasRangeFormulaMG1MG1=CEILING(MD4,25)MM1,MS1MM1=MG1+25MD4MD4=CEILING(MD5,25)-25MD147MD147=CEILING(MD146,25)


How to accomplish please?


----------



## hsandeep (Dec 26, 2022)

Please don't consider thread#1 instead below
‘Major’ play area for the code is MG1:QT156
There are 20 ‘*headers*’ MG1; MM1; MS1 all the way till QQ1
Below each of such ‘*headers*’ for example, ‘*header*’ MG1; there is a range of 4 columns MG147:MU156. Out of this range MG147:MU156, MG147:MG156 *HasFormula* & gets value from MH147:MH156 (each cell of MH147:MH156 gets updated from another cells based on real time feeds). MG147:MG156 *HasFormula* of *[R1C1]* type like in MG147*=MH147* & so on up to MG156*=MH156*. MU147:MU156 by default contains “” (blank).

Actions to be performed by code:

IF ‘*1st header*’ MG1<=$MD$2, then code should *REMOVE THE FORMULAS* from MG2 *AND ALSO FROM* MG147:MG156 and *SHOULD FILL A VALUE 20* in MJ147:MJ156.
Repeat Step 2 for all the balance ‘*19 headers*’ with analogous *REMOVAL OF THE FORMULAS* from 1 CELL BELOW THE ‘*HEADER*’ i.e. MM2 *AND ALSO FROM* MM147:MM156 and *SHOULD FILL A VALUE 20* in MP147:MP156 *PROVIDED* the ‘*2nd header*’ MM1<=$MD$2 & so on till ‘*19th header*’ MM1<=$MD$2 *PROVIDED* the ‘*RESPECTIVE HEADER*’ is<=$MD$2
Now if MD4 changes , it will change all the ’*20 HEADER*’ values from MM1:QQ1 IF MD4 has *changed* (it will always decrease but will never increase at all), then the code should *MATCH THE 2nd ROW* with the ‘*HEADER*’ value and accordingly *FILL THE CELL BELOW* THE ‘*MATCHED HEADER*’ *AND ALSO FILL* the 1st column below the ’*MATCHED HEADER*’ with the 1st column value *AND* *SHOULD FILL A VALUE 20* in the 4th column of the ’*MATCHED HEADER*’.
Repeat step 3 till the ‘*HEADER’s*’ value >MD2.
Lastly, if $ME$1>=(now), then *PUT THE FORMULAS BACK* in all the ‘*header’s*’ *2nd ROW* cell (i.e. for *header* cell MG1 *2nd ROW* cell is MG2 (here MG2 should have the formula =MG1), *AND* MG147:MG156 *SHOULD NOW CONTAIN FORMULA IN [R1C1] *i.e. MG147*=MH147* *AND* values of the 4th column, here MJ147:MJ156 *SHOULD BE FILLED WITH “” (BLANK)*.
Repeat step 5 till the ’*LAST HEADER*’ i.e. QQ1
How to accomplish please?
Book105.xlsxMDMEMFMGMHMIMJMKMLMMMNMOMPMQMRMSMTMUMV125/12/2022  1:22:00 PM41575416004162524162741575416004162534415755415981464164914741650536.00536.0020470.00470.0020555.00555.0020148326.00326.0020405.00405.0020666.00666.0020149456.00456.0020357.60357.6020777.00777.0020150256.00256.0020295.00295.0020295.00295.0020151230.00230.0020248.35248.3520248.35248.3520152203.00203.0020203.00203.0020203.00203.0020153169.25169.2520169.25169.2520169.25169.2520154137.50137.5020137.50137.5020137.50137.5020155116.00116.0020116.00116.0020116.00116.002015628.4028.402028.4028.402028.4028.4020PCell FormulasRangeFormulaMG1MG1=CEILING(MD4,25)MG2,MS2,MM2MG2=MG1MM1,MS1MM1=MG1+25MD4MD4=CEILING(MD5,25)-25MD147MD147=CEILING(MD146,25)


----------

