Want to unlink calculations

E

electricbluelady

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?
 
O

Otto Moehrbach

It appears to be in the way your macros are written. Post back and include
the code (macros). HTH Otto
 
E

electricbluelady

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 said:
It appears to be in the way your macros are written. Post back and include
the code (macros). HTH Otto
Click to expand...
 
O

Otto Moehrbach

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can VLOOKUP return a formula?? 2
Data Group Total 3
Converting Text to Numbers 3
Calculations within a report 1
Cumulatively list data from each new worksheet 1
How do I create a formula in excel to multiply two items from two 2
I want to count occurences between 2 dates. 4
Excel calculation error on subtraction 1

Top