Want to unlink calculations
"Otto Moehrbach" wrote in message news:...
You say:
"Here is the problem: If I click outside of the invoice (or defined
fields),
or do a quick addition/subtraction formula, all my figures change in my
fields that were calculated by a macro. How do I 'break' the link?"
There is no "link" in the code you posted. For calculations to take place
when you "click" somewhere, you would have to have an event macro that
fires automatically when the "click" is made. The code you posted does
not contain any event macros. Does your file contain any sheet or
workbook event macros? Exactly what do you do to cause this "all my
figures change"? You say you click some place or do a quick formula. Post
back and give me a step-by-step of what you do. HTH Otto
"electricbluelady" wrote in
message ...
Hi Otto,
I have the code for two of the four here. Thank you so much!
'Sub NeedHelp = the first invoice
'Sub NeedMoreHelp=the second invoice
Sub NeedHelp()
'Turns off screen flicker while running macro
Application.ScreenUpdating = False
'Declarations
Const B75 As Long = 54315
Const D75 As Long = 26040
Const F75 As Long = 26102
Const J75 As Long = 28275
Const K75 As Long = 27785
Const L75 As Long = 0
Const M75 As Long = 0
Const N75 As Long = 54315
Const O75 As Long = 53375
'This calculates ML
Range("USGML").Value = B75
Set TargetRange = Range("B44:B74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(B75-(sum(B44:B74)))"
End If
Next
'This calculates Tier 1
Range("USGTier1").Value = D75
Set TargetRange = Range("D44:D74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(D75-(sum(D44:D74)))"
End If
Next
'This calculates MLV ML
Range("USMLVML").Value = F75
Set TargetRange = Range("F44:F74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(F75-(sum(F44:F74)))"
End If
Next
'This calculates Swing ML
Range("USSwingML").Value = J75
Set TargetRange = Range("J44:J74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(J75-(sum(J44:J74)))"
End If
Next
'This calculates Swing CG
Range("USSwingCG").Value = K75
Set TargetRange = Range("K44:K74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(K75-(sum(K44:K74)))"
End If
Next
'This calculates Offsale ML
Range("USOffsaleML").Value = L75
Set TargetRange = Range("L44:L74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(L75-(sum(L44:L74)))"
End If
Next
'This calculates Offsale CG
Range("USOffsaleML").Value = M75
Set TargetRange = Range("M44:M74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(M75-(sum(M44:M74)))"
End If
Next
'This calculates Total ML
Range("TotalML").Value = N75
Set TargetRange = Range("N44:N74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(N75-(sum(N44:N74)))"
End If
Next
'This calculates Total CG
Range("TotalCG").Value = O75
Set TargetRange = Range("O44:O74")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(O75-(sum(O44:O74)))"
End If
Next
Application.ScreenUpdating = True
End Sub
Sub NeedMoreHelp()
'Turns off screen flicker while macro is running
Application.ScreenUpdating = False
'Declarations
Const B153 As Long = 9426
Const D153 As Long = 3100
Const F153 As Long = 4030
Const H153 As Long = 2325
Const J153 As Long = 9291
Const N153 As Long = 261
Const O153 As Long = 258
Const P153 As Long = -29
Const Q153 As Long = -28
Const R153 As Long = 9426
Const S153 As Long = 9263
'This calculates ML
Range("MMML").Value = B153
Set TargetRange = Range("B122:B152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(B153-(sum(B122:B152)))"
End If
Next
'This calculates Tier 1
Range("MMTier1").Value = D153
Set TargetRange = Range("D122:D152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(D153-(sum(D122:D152)))"
End If
Next
'This calculates Tier 2
Range("MMTier2").Value = F153
Set TargetRange = Range("F122:F152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(F153-(sum(F122:F152)))"
End If
Next
'This calculates Tier 3
Range("MMTier3").Value = H153
Set TargetRange = Range("H122:H152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(H153-(sum(H122:H152)))"
End If
Next
'This calculates MLV ML
Range("MMMLV").Value = J153
Set TargetRange = Range("J122:J152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(J153-(sum(J122:J152)))"
End If
Next
'This calculates Swing ML
Range("MMSwingML").Value = N153
Set TargetRange = Range("N122:N152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(N153-(sum(N122:N152)))"
End If
Next
'This calculates Swing CG
Range("MMSwingCG").Value = O153
Set TargetRange = Range("O122:O152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(O153-(sum(O122:O152)))"
End If
Next
'This calculates Total Offsale ML
Range("MMOffsaleML").Value = P153
Set TargetRange = Range("P122:P152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(P153-(sum(P122:P152)))"
End If
Next
'This calculates Total Offsale CG
Range("MMOffsaleCG").Value = Q153
Set TargetRange = Range("Q122:Q152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(Q153-(sum(Q122:Q152)))"
End If
Next
'This calculates Total ML
Range("MMTotML").Value = R153
Set TargetRange = Range("R122:R152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(R153-(sum(R122:R152)))"
End If
Next
'This calculates Total CG
Range("MMTotCG").Value = S153
Set TargetRange = Range("S122:S152")
For Each Cell In TargetRange
If Cell.Value = "" Then
Cell.Value = "=(S153-(sum(S122:S152)))"
End If
Next
Application.ScreenUpdating = True
End Sub
--
Thank you,
Electricbluelady
"Otto Moehrbach" wrote:
It appears to be in the way your macros are written. Post back and
include
the code (macros). HTH Otto
"electricbluelady" wrote in
message ...
Hi Everyone,
I have a spreadsheet with four different invoices on it. Each invoice
has
a
separate macro to perform calculations. Each range used in the
calculations
is named.
Here is the problem: If I click outside of the invoice (or defined
fields),
or do a quick addition/subtraction formula, all my figures change in
my
fields that were calculated by a macro. How do I 'break' the link?
--
Thank you,
Electricbluelady
|