Calculating in the backround

J

justmcd

New member
Joined
Jan 3, 2012
Messages
39
Reaction score
0
Points
0
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
 

Attachments

  • Analysis RevX.xlsm
    23.5 KB · Views: 27
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.
 
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
 
Hi CheshireCat

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



Regards
 

Attachments

  • Analysis RevX.xlsx
    12.4 KB · Views: 22
Hi,

Try replacing all your code with:
Code: 
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
 
Hi JoePublic
urs and nothin
I have placed my code with yours and nothing happens. Any suggestion?

Regards
justmcd
 
Where did you put the code? It needs to be in the worksheet module. It worked fine for me.
 
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
 
Sure - here it is.
 

Attachments

  • Analysis RevX.xlsm
    29.1 KB · Views: 26
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
 
Oh, I see. In that case the code should be:
Code: 
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
    End If
    If 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
 
Thanks a million JoePublic, it works like a charm.
 
Great - thanks for letting me know. :)
 
You must log in or register to reply here.
Back
Top