How to update formulas in the macro

[vkaruk]

Hello
First of all, I would like to thank everyone who contributed to this forum. I would like to ask a question:
When we type a formula in a cell, for example:

D1=B1+C1

when we add a column before column B the formula returns to

E1=C1+D1

So that when a column or row added, formulas updated automatically.
My question is:

Is this automatically updating possible in macro code when we do such calculations with vba macros. For example, our code is

Range ("D1") = Range ("B1") + Range ("C1")

Then we add a column before column B, is it possible to update the formula like

Range ("E1") = Range ("C1") + Range ("D1")

I can handle this with find & replace, but if there are dozens of formulas in the code, it's a very difficult process when adding or deleting columns or rows.

Thanks…

[dflak]

There are several ways around this problem.

If your data are tabular then convert it to an Excel Table. When you use Excel Tables in formulas or VBA, you can refer to columns by the column header name.

A more general way, but it takes more work, is to give the cells in question names. So Cell D1 can be called "Sell Price", Cell B1, "Cost" and Cell C1, "Profit." Then the code becomes:
[code]
Range ("Sell Price") = range("Cost") + range("Profit")
[code]
You can insert any number of rows or columns or even cut / paste the cells and the code will know where to find the pieces.

I can give you better guidance with a sample workbook.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[vkaruk]

Thanks a lot,

It works in single cells, but i couldn't apply that way in following code, what should i do?

For i = 1 To 20

Range("D" & i) = Range("B" & i) + Range("C" & i)

next i

[dflak]

I still need to see the workbook. Why are you doing this in VBA? What is the overall objective? You are showing me only a tiny piece of a bigger picture. What do columns D, B and C represent?

[Zer0Cool]

It works in single cells, but i couldn't apply that way in following code, what should i do?

You will need to update your code to work with whatever method you use to handle the scenario.

To build on what @dflak has suggested, essentially you can accomplish this in a variety of ways:

• Instead of static reference use dynamically generated references in VBA. For example using an anchor point to identify each column, like the header. So for example if your header was in B1 and is now in C1, having code that stores the location of that specific header by finding it on row 1.
• Using named ranges. Similar to the above except you name a header or column and then reference the named range in your code. When a cell shifts, the named range will stick to the data.
• Using a table (listobject) and then using the listobject's child objects/methods and properties instead of ranges. IE: databodyrange, ListRows, ListColumns, etc.

Most of the approaches will require you to adjust your process to fit the method. So for example:

Please Login or Register  to view this content.

might become (psuedo code)

Please Login or Register  to view this content.

You may even end up looping objects instead of using a counter. IE: "For each rng in listobject.listcolumns(4).databodyrange..." (again psuedo code, the syntax and properties/methods/objects may be different).

Honestly using tables for tabular data and using the listobject in VBA is the best method. Tables in general provide many benefits from visual aides to easier formulas using structured references. The benefit extends to working with tables in VBA.

[Zer0Cool]

As an extension of my last post, here is a snippet from some code I just wrote the other day including comments.

The logic is this dumps info about files in a loop into a table, columns 1/2/3 in a table appending a new row to the bottom.

My counter "lngR" starts at 0 outside the loop and gets incremented each time the loop iterates. If the table has less rows than the counter, I add a row to the table. I then use the last row of the table, "ListRows(lngR)" and using its range method assign the value to the 1st, 2nd and 3rd columns of that row in the table. I then loop to the next file until all the files in my source folder have been processed.

There are likely 20 different alterations I could make to this approach but this is what I came up with at the time. Maybe it will give you some context.

Please Login or Register  to view this content.

[vkaruk]

Thanks, dflak and Zer0Cool

I've solved my problem with your help. I converted my tabular data to excel table then used this code

For i = 1 To 20

With Worksheets("Sheet1").ListObjects("Table1")

.DataBodyRange(i, 3) = .DataBodyRange(i, 1) * .DataBodyRange(i, 2)

End With

Next i