Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Want to unlink calculations
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,071
Default 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







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pages in a worksheet linked, how to unlink?? Meenie Excel Worksheet Functions 2 April 22nd 23 10:10 AM
Want to unlink calculations electricbluelady Excel Worksheet Functions 3 June 26th 08 01:38 PM
Unlink a file ?? Kane New Users to Excel 3 February 5th 06 08:01 PM
How do I unlink cells in M.S. 2003 Access? They link when my stud. Tea Excel Discussion (Misc queries) 1 January 3rd 05 06:25 PM
How do I unlink cells in different worksheets when there are no a. kbigs Excel Worksheet Functions 1 December 1st 04 02:22 AM


All times are GMT +1. The time now is 02:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement