Balancing VBA Code

iellingson

New Member
Joined
Dec 22, 2009
Messages
14
Looking for some VBA code to run behind the scenes to dynamically refresh certain fields (including themselves) based upon the most recent key entry. An example of this is on row 3 column K I could enter $200 and columns G-L would all refresh to reflect the $200 input, however on the same row I could change column L and enter 15% and it would then change all values in column G-L to reflect the output from that input. Columns K & L technically overlap one is a $ while the other is a %. Example attached.

In the example I have forced column J to look at either column K or L based upon my entry, ideally I would like this to dynamically update based upon which field (K or L) the values were entered in most recently.

Thanks in advance.

Book1
ABCDEFGHIJKLMNOPQRST
1BASEADJUSTED
2Field AField BField CField DField EField FField GField HField IField JField KField L
38020.29 $2.11 $232.58 $1,689.85 $1,922.43 $2.36 $1,889.85 $232.58 $2,122.43 $200.00 9.42%I would like to be able to change either column K or column L and have it auto recalculate columns G-L based upon whichever was most recently updated. Hardcoded Static formula shouldn't change with Column K/L Inputs Columns that I want to refresh based upon inputs from Column K & L
47870.29 $2.26 $228.23 $1,774.85 $2,003.08 $2.54 $1,997.41 $228.23 $2,225.64 $222.56 10.00%
52610.29 $2.69 $75.69 $703.09 $778.78 $3.08 $803.09 $75.69 $878.78 $100.00 11.38%
63340.29 $2.74 $96.86 $914.52 $1,011.38 $3.04 $1,014.54 $96.86 $1,111.40 $100.03 9.00%
7550.29 $5.99 $15.95 $329.55 $345.50 $6.90 $379.55 $15.95 $395.50 $50.00 12.64%
81980.29 $2.96 $57.42 $585.09 $642.51 $3.36 $664.50 $57.42 $721.92 $79.41 11.00%
92530.29 $2.73 $73.37 $691.05 $764.42 $3.13 $791.05 $73.37 $864.42 $100.00 11.57%
106140.29 $2.41 $178.06 $1,478.06 $1,656.12 $2.64 $1,622.07 $178.06 $1,800.13 $144.01 8.00%
112210.29 $2.68 $64.09 $591.48 $655.57 $3.04 $671.48 $64.09 $735.57 $80.00 10.88%
125410.29 $2.09 $156.89 $1,130.09 $1,286.98 $2.24 $1,212.24 $156.89 $1,369.13 $82.15 6.00%
Sheet1
Cell Formulas
RangeFormula
D3, D12, D11, D10, D9, D8, D7, D6, D5, D4D3=A3*B3
E3, E12, E11, E10, E9, E8, E7, E6, E5, E4E3=C3*A3
F3, F12, F11, F10, F9, F8, F7, F6, F5, F4F3=SUM(D3:E3)
G3, G12, G11, G10, G9, G8, G7, G6, G5, G4G3=H3/A3
H3, H12, H11, H10, H9, H8, H7, H6, H5, H4H3=J3-I3
I3, I12, I11, I10, I9, I8, I7, I6, I5, I4I3=A3*B3
J3, J11, J9, J7, J5J3=F3+K3
L3, L11, L9, L7, L5L3=K3/J3
J4, J12, J10, J8, J6J4=(F4/(1-L4))
K4, K12, K10, K8, K6K4=J4-F4
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Hi, I just noticed this ... it's been unanswered for a while. Have you solved this yet?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,960
Members
410,586
Latest member
acadavid86
Top