For Loop with Evaluate, How do I Correctly Reference Cells

[FormatSort]

Long time Excel user but I'm new to VBA as I never have an occasion to need it until now. My application is a Finance Budget, I was able to get Solver running in VBA for one of my equations. However, what seemed to be the simpler equation is giving me fits.

My basic spreadsheet setup is 27 columns wide, for each annual budget, 2019, 2020, etc. I'm simply trying to calculate a Withdrawal (row 98) = Year End Balance (row 145) - Minimum Required Balance (row 146). The VBA is to automate this calculation and then do it again for each annual budget, once, in each successive column (this cannot be done as a formula within Excel as it causes a circular reference). The repeating columns are G98:AG98 (For column = 7 To 33). My problem seems to be in the code to the right of the Evaluate, I'm simply trying to do G98 = G145-G146, and then repeat for each column, but clearly, even after reading a couple dozen examples I cannot the Cells(row, column) address correct for the calculation. I show a couple different non-working and working lines in the code with comments.

Any insight where I've gone wrong is greatly appreciated.

My code is:

Please Login or Register  to view this content.

[protonLeah]

Please Login or Register  to view this content.

[FormatSort]

Hello Ben,

THANK YOU, absolutely amazing, that did the trick, I should have posted days ago instead of beating my head against the wall for so long. Now I can move on to get the rest of the spreadsheet working.

Best Regards, FormatSort

[Fluff13]

Another option

Please Login or Register  to view this content.

[FormatSort]

Hello Fluff13,

Thank you for the reply, I'm glad to learn alternative options.

Best Regards

[Fluff13]

You're welcome & thanks for the feedback

[FormatSort]

Ben,

One more question please, is there a way in VBA to handle when I insert or delete rows or columns so VBA auto updates to the correct reference? I tested by inserting one line above line 98, so the formula starts at line 99, but VBA did not update. Is there a trick in how I reference things to get this behavior?

Best Regards

[Fluff13]

How do you determine which row you need this in?
Is there a specific word or phrase on that row, which does not appear anywhere else?

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[FormatSort]

How do you determine which row you need this in?
Is there a specific word or phrase on that row, which does not appear anywhere else?

Yes, Every Row in Column B contains a Description of each line. Every Column, in Row 3, contains the Year.

Is there a way to reference these in the code so when I add or delete a row or column it will automatically update?

Please Login or Register  to view this content.

[Fluff13]

What are the values?
It would be best to supply as workbook as mentioned by protonLeah

[FormatSort]

What are the values?
It would be best to supply as workbook as mentioned by protonLeah

I'm unable to provide a copy of the Workbook due to the proprietary data it contains. I strive to make my posts self explanatory due to this, sorry if I missed the mark.

Let me give some further details:

Minimum Required Balance (row 146) { Example: $10,000 }
Code: Cells(146, columnIndex).Value

Year End Balance (row 145) - { Example: starting value $3,000 } >>> Result after Macro runs: $10,000
Code: Cells(145, columnIndex).Value -

Withdrawal (row 98) = { Example: starting value zero } >>> Result after Macro runs: $7,000
Code: Cells(98, columnIndex).Value =

I was not looking for specific details on how to maintain Relative References for this example only, but I'm trying to learn what is possible in VBA so when I add or delete rows or columns the Relative References are maintained.

[Fluff13]

VBA is totally separate from Excel, so it's not like a formula that will adjust as rows/columns are added/deleted.
You need a method of defining your range within the code.
So if "Withdrawal" is only found on row 98 you can use Range.Find to locate it & set the row.
If "Withdrawal" can be found in a number of different rows, then you will need to find another way of locating the correct row.

[FormatSort]

Excellent, thank you for the information.

[Fluff13]

You're welcome