Calculating in the backround

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

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
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

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

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

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
239
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
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
 

justmcd

New member
Joined
Jan 3, 2012
Messages
39
Reaction score
0
Points
0
Hi JoePublic
urs and nothin
I have placed my code with yours and nothing happens. Any suggestion?

Regards
justmcd
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
239
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
Where did you put the code? It needs to be in the worksheet module. It worked fine for me.
 

justmcd

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

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
239
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
Sure - here it is.
 

Attachments

  • Analysis RevX.xlsm
    29.1 KB · Views: 26

justmcd

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

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
239
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
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
 

justmcd

New member
Joined
Jan 3, 2012
Messages
39
Reaction score
0
Points
0
Thanks a million JoePublic, it works like a charm.
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
239
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
Great - thanks for letting me know. :)
 
Top