PDA

View Full Version : Calculating in the backround



justmcd
2012-03-22, 05:22 PM
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
2012-03-25, 02:06 AM
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
2012-03-25, 07:23 PM
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
2012-03-25, 07:25 PM
Hi CheshireCat

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



Regards

JoePublic
2012-03-26, 01:15 PM
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
2012-04-01, 08:30 PM
Hi JoePublic
urs and nothin
I have placed my code with yours and nothing happens. Any suggestion?

Regards
justmcd

JoePublic
2012-04-01, 09:34 PM
Where did you put the code? It needs to be in the worksheet module. It worked fine for me.

justmcd
2012-04-01, 09:45 PM
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
2012-04-01, 10:00 PM
Sure - here it is.

justmcd
2012-04-01, 10:29 PM
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
2012-04-01, 11:31 PM
Oh, I see. In that case the code should be:

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
2012-04-02, 11:17 AM
Thanks a million JoePublic, it works like a charm.

JoePublic
2012-04-02, 11:24 AM
Great - thanks for letting me know. :)