Calculating in the backround

[justmcd]

Hi

I have spreadsheet whereby to eliminate clashes with circular references I have written a VBA code (I am still learning VBA programming) to copy and paste special in cell B3 to C3 in order to calculate OD in B4. When ever I change B1 or B9 it calculate but taking close to a minute and times whn working on othe cells the vba will still run preventing me to work on other cells.

Like I said I am still learning VBA and the coding might be a bit untidy. Can you please assit to stop the running will I work on cell not related to the code or a better coding to to perform what want to achieve (See attached).

Regards

[CheshireCat]

When attempting to open your attached file, this warning appears:

"The file you are opening is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file."

You might want to check your file.

[justmcd]

Hi CheshireCat

The workbook is in a macro enabled, hence the security prompt.

I have changed it to excel format but can not reattach it on this reply. I will have to paste it on a new thread.



Regards

[justmcd]

Hi CheshireCat

The workbook is in a macro enabled, hence the security prompt.
I have change it to anormal workbook.



Regards

[JoePublic]

Hi,

Try replacing all your code with:

Private Sub Worksheet_Change(ByVal Target As Range)    On Error GoTo oops


    If Not Intersect(Target, Range("b1")) Is Nothing Then
        Application.EnableEvents = False
        Range("C1").Value = Range("B1").Value
        Application.EnableEvents = True
    ElseIf Not Intersect(Target, Range("B3").Precedents) Is Nothing Then
        Application.EnableEvents = False
        Range("C3").Value = Range("B3").Value
        Application.EnableEvents = True
    End If


    Exit Sub


oops:
    Application.EnableEvents = True
End Sub

[justmcd]

Hi JoePublic
urs and nothin
I have placed my code with yours and nothing happens. Any suggestion?

Regards
justmcd

[JoePublic]

Where did you put the code? It needs to be in the worksheet module. It worked fine for me.

[justmcd]

Hi JoePublic

I have placed it on the worksheet module and still nothing. Can you please post your excel file with the working code.

Regards

[JoePublic]

Sure - here it is.

[justmcd]

Hi JoePublic

Still you worksheet does not do what I want. If B1 =1100,B3=12. To avoid circular referencing I copied and pasted special B3 as value in C, by adding C1 to twice C3 I should get B4=1124. If I add B1 and B3 I get circular reference error message, hence I opted for the method above.

Regards
justmcd