Table Address (Not Cell)

[kjwjr]

Hi All,

I've been working on this Bill-Of-Materials Macro for work to automatically calculate the roll-up. It's not been fun. I recently updated it, so it puts in a formula, so you can trace how the cost is being calculated. Let me get to the point, I'm currently using the following to get the cell address:

tbl.DataBodyRange(j, intOutputColumn).Address(False, False)

This will give me something in the format of A1. The problem is when I sort it, the formulas get all messed up because they are global. I'm not sure if it's even possible, but could I somehow reference a cell in a table that updates properly when the column is sorted? To be clear, the cells I'm reference are not in the same row.

I did have this working where it put in a value, but then it doesn't actively update and there's no traceability. Of course, the sort function would work fine though.

Thanks in advance for any help.

-Kris

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[kjwjr]

Thanks. I should have done that earlier. I think I've attached it now.

I did realize that maybe this isn't necessarily a VBA question. The excel document I'm posting is effectively the output of the script. You'll notice when you sort it, the formulas get out of order. I'm going to continue to dig into this, but from a different angle. Any help would be greatly apprecated.