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 the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,118,994
Messages
5,575,423
Members
412,661
Latest member
joelgibney1
Top