Add Row Cell Number Changes though VBA doesn't

[ndjustin20]

Hello Everyone,

I'm new to your forum though not completely new to VBA or Excel. I am working in Excel 2007 utilizing VBA scripts that I've built for a spreadsheet that I have uploaded here www.shingleme.com/TestTest.xlsm because I couldn't find an upload button on here anywhere . The problem I am running into is the user can insert new rows which throws off my calculations and tables that are generated using VBA. On the right hand side of the sheet you'll notice a sort of "Dash Board" that does some calculations and analysis. I want to put that information on the top though I want to keep it free floating not actually inside a cell and have the formula's updated if a row is added. Is there a way to force VBA to update when the cell changes like when a cell changes in Excel and it automatically updates the formula? I would think there would be but for the life of me i can't figure out how to do it.

Thank you in advance for any and all help

Justin

 

[Andrew Poulsom]

Welcome to MrExcel.

If you name the ranges and use those names instead of cell references in VBA your macro will adjust automatically.

 

[ndjustin20]

Ok, This is probably going to sound like the dumbest question ever asked though here goes....how do I call a named range then in VBA? Also, does that mean I have to name each cell I want to calculate as a Range. So as an example. Say I have A1 + A2 in VBA though want to have it update if A1 suddenly turns into A2 and A2 into A3. Ok so I would have to name the cell then as a named range? So it would be Range("RangeNameA1") + ("RangeNameA2") or is this way off the mark? I'm sorry if this sounds completely crazy I'm a little confused on how to make this work and if you had maybe an example that would HELP A TON Thank you very much for you feedback thus far

I figured it out......THANK YOU THANK YOU THANK YOU FOR YOUR HELP TODAY YOU ARE AWESOME!!!!!!!!!!!!!!!!!!!!



Justin