Offset formula references (only active sheet's)

[Grho]

Hello,

In my Excel I'm transposing large set of data and I need to keep references as they were before transposing. For this I need following:

I want to offset references in formula in my VBA code, doesn't matter if absolute or relative, but offset only references that are on current sheet. References to other sheets should remain as originaly were.

Example:

= A1 + $B$1 + KF1001 + SUM(C2:C5) + sheet2'SUM(C2:C5)

I have offset (3,2)

Result should be

= C4 + $D$4 + KH1004 + SUM(E5:E8) + sheet2'SUM(C2:C5)

Can somebody help me with coding? I'm stuck.

Thank you, Grho

[6StringJazzer]

I would like to get a more detailed description of the overall problem you are trying to solve, because I suspect that using VBA to modify worksheet formulas to apply an offset is not the best way solve your overall problem. Parsing and modifying worksheet formulas in VBA is certainly possible but more complicated than meets the eye and I'll bet if we start from the beginning we can get you a better solution.

It would especially help if you attach your file: The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[Grho]

Hello,

Thank you for answer.
OK, I attached a test example, but you will get the idea. The final solution is a sales budget simulation/planning solution with close to 1000 people and 12 salary subitems.

In the attached excel, in Salary sheet is the data. With macro Trasform(2) I trasnspose this data to Sheet Final. The column G:G is the tricky one and in the test example I just manualy fixed the formulas (although in real case there are some more).
I also started bulidling macro Testing(), to parse/transpose formula (this macro will finaly be a function in Trasform(2)). My idea was to parse references, then offset them accordingly, and then REPLACE the original references in formula with offseted ones.

One problem is that my regex is still not OK parsing references that point to other sheet. But more concerning is, that if I pass the REPLACE on formula string, and the references in formula will point on the sheet and the same also on the other sheet, I will do the wrong replacement (because the reference to other sheet must stay the same as original).

So I'm looking for some more clever approach to solve this formula offseting.

I hope I was clear enough.

Thank you for help.

G