Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Calculating in the backround

  1. #1

    Calculating in the backround



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Acolyte CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    63
    Articles
    0
    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.

  3. #3
    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

  4. #4
    Hi CheshireCat

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



    Regards
    Attached Files Attached Files

  5. #5
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    160
    Articles
    0
    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
    Circumference of a circle = 2πrē



    ēthe circle's radius

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

    Regards
    justmcd

  7. #7
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    160
    Articles
    0
    Where did you put the code? It needs to be in the worksheet module. It worked fine for me.
    Circumference of a circle = 2πrē



    ēthe circle's radius

  8. #8
    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

  9. #9
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    160
    Articles
    0
    Sure - here it is.
    Attached Files Attached Files
    Circumference of a circle = 2πrē



    ēthe circle's radius

  10. #10


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •