We help IT Professionals succeed at work.

Adjustment to Macro for formula instead of data

Bright01
Bright01 asked
on
I have a macro that takes data/content from a cell when it changes and moves it to another WS.  Fairly simple.  BUT, the problem I have is that if I have a formula in the cell (B5), it will not pass the change over.

Thank you in advance.  B.

Private Sub Worksheet_Change(ByVal Target As Range)
       Application.ScreenUpdating = False
Select Case Target.Address
    Case "UseCase"
       End Select
       On Error GoTo 0
    Application.ScreenUpdating = True
'    End Select
If Not Intersect(Target, Range("B5")) Is Nothing Then
            Sheets("Financial Business Case").Range("e40") = Target.Value
        End If
End Sub
Comment
Watch Question

Commented:
Sheets("Financial Business Case").Range("e40").Formula = Target.Formula
Top Expert 2016
Commented:
Hi,

Pls try to replace
Sheets("Financial Business Case").Range("e40") = Target.Value

with

Sheets("Financial Business Case").Range("e40").Formula = Target.Formula

Regards

Author

Commented:
Rgonzo and Kimputer,

Great work but one problem.  When I added the line, it transfers the formula.  I just want the result transferred.....not the formula in B5.  

B.
Rob HensonFinance Analyst
Commented:
I take it you mean when the formula result in B5 changes, you want the new result to move.

I suspect this isn't happening at the minute because the Change Event is getting triggered but because the Target cell that has changed to impact the formula was not B5 then the move isn't getting triggered.

If you go into B5, press F2 and then Enter, does the move then occur?

Thanks
Rob H

Author

Commented:
Rob,

Greetings.  YES.  If I go to B5 and press F2, it moves the formula to E40.  And you are correct.  I'm trying to move the result of the formula in B5, not the formula itself.

b.
Rob HensonFinance Analyst

Commented:
So the line in the VBA script needs to end .Value as previously written.

However, I suspect that you need to change the target cell for invoking the move, not the cell that includes the formula but at least one of the cells that feed the formula instead. Then if one of the inputs to the formula changes the result of the formula will move to the other sheet.

Is there a reason why the cell on the destination sheet isn't just a formula linking back to the cell on the source sheet?

Author

Commented:
Yes.  So what this model does is there is a formula in B5:B10 that as you select a sentence on another WS, it places the selection in the cell (B5:B10).  So I'm simply trying to take the result and move it into another WS, "Financial Business Case".Range("e40") = Target.Value.  Through e40:e45.

Make sense?

If I change

Sheets("Financial Business Case").Range("e40").Formula = Target.Formula
to
Sheets("Financial Business Case").Range("e40").Formula = Target.Value

Will that do the trick?

B.

Author

Commented:
Thanks guys.....got it to work......

B.