Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Syntax Again
My macro involves operations where I cut and paste from a live cell(s)
to a hard copy cell(s) which I use to avoid iterative programming. I keep cutting and pasting values until a differecne cell(s) is close enough to zero. Also, I need to goal seek a couple of cells as well. Usually, it works flawlessly but in this case I have a number of such instances and the interaction seems to make it not work very well. Right now, my order is a little haphazard. When I do things manually, I manage to make it work and my approach is to go after the differecne cells with the highest values, which is kind of logical. Can someone throw a few commands in my macro below that would tell it to look for the highest difference value first and work on that one? Then, on to the next one? Also, I am not sure my syntax is correct in all cases. I watch some of the variables as I step through the macro and Differecne7, which is supposed to be the sum of all the others does not seem to always change when one of its component changes. So could you also check my syntax please? Shouldn;t all this be live as you step through it? Keep in mind that running one "ROUTINE" below can casue the vlaue in a prior routine to change, so it isn't compeltely straightforward but, as I've said, manually I am able to make it work. I may have anextra variable or two in there, but please ignore that. Difference7 is the sum of a bunch (ten) of differences that each need to be close to zero. Thanks you so much. Here is the macro: Sub CopyPasteandGoalSeekProcedure() Dim Difference0 As Double Dim Difference00 As Double Dim Difference000 As Double Dim Difference As Double Dim Difference1 As Double Dim Difference2 As Double Dim Difference3 As Double Dim Difference4 As Double Dim Difference5 As Double Dim Difference6 As Double Dim Difference7 As Double Dim AandDIntPaidMethodEcho As String Dim AandDEndBalanceLive As Double Dim ConstIntPaidMethodEcho As String Dim ConstEndBalanceLive As Double Dim AandDIntReserveEndBalance As Double Difference0 = 10 Difference00 = 10 Difference000 = 10 Difference = 10 Difference1 = 10 Difference2 = 10 Difference3 = 10 Difference4 = 10 Difference5 = 10 Difference6 = 10 Difference7 = 10 5 Difference0 = Range("AandDIntReserveEndBalance").Value Difference00 = Range("ConstIntReserveEndBalance").Value 'Dim CurrentInterestPaymentPaste As Double 'Dim CurrentInterestPayment As Double Difference7 = Abs(Difference00 + Difference0 + Difference000 + Difference + Difference1 + Difference2 + Difference3 + Difference4 + Difference5 + Difference6) 'FIRST ROUTINE While Difference4 0.1 Range("InvestorEquityPaste").Value = _ Range("InvestorEquityLive").Value Difference4 = Range("InvestorEquityDifference").Value Wend If (Difference7 < 2) Then GoTo 20 'TEST IF DONE '2ND ROUTINE While Difference 0.1 Range("FutureCostsPaste").Value = _ Range("FutureCostsLive").Value Difference = Range("ZeroCheckFutureCostSubstitution").Value Wend If (Difference7 < 2) Then GoTo 20 '3RD ROUTINE If AandDIntPaidMethodEcho = "Pay Current" Then GoTo 100 If AandDIntPaidMethodEcho = "Accrue" Then GoTo 100 Application.Goto Reference:="AandDEndBalanceLive" Range("AandDEndBalanceLive").GoalSeek Goal:=0.1, ChangingCell:=Range("AandDIntReserveBB") 100 Difference5 = Range("AandDEndBalanceLive").Value If (Difference7 < 2) Then GoTo 20 '4TH ROUTINE While Difference1 0.1 Range("TotalLoanAndReservePaste").Value = _ Range("TotalLoanAndReserveLive").Value Difference1 = Range("TotalLoanAndReserveDifference").Value Wend If (Difference7 < 2) Then GoTo 20 '5TH ROUTINE While Difference2 0.1 Range("AandDFeePaste").Value = _ Range("AandDFeeLive").Value Difference2 = Range("AandDFeeDifference").Value Wend If (Difference7 < 2) Then GoTo 20 '6TH ROUTINE While Difference3 0.1 Range("ConstFeePaste").Value = _ Range("ConstFeeLive").Value Difference3 = Range("ConstFeeDifference").Value Wend If (Difference7 < 2) Then GoTo 20 '7TH ROUTINE If ConstIntPaidMethodEcho = "Pay Current" Then GoTo 200 If ConstIntPaidMethodEcho = "Accrue" Then GoTo 200 Application.Goto Reference:="ConstEndBalanceLive" Range("ConstEndBalanceLive").GoalSeek Goal:=1.01, ChangingCell:=Range("AandDIntReserveBB") 200 Difference6 = Range("ConstEndBalanceLive").Value Difference7 = Abs(Difference00 + Difference0 + Difference000 + Difference + Difference1 + Difference2 + Difference3 + Difference4 + Difference5 + Difference6) '8TH ROUTINE Application.Goto Reference:="FutureCostsLive" Selection.Copy Range("FutureCostsPaste").Select Range(Selection, Selection.End(xlToRight)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False If (Difference7 < 2) Then GoTo 20 Else GoTo 5 ' WILL THIS ALLOW IT TO LOOP BACK TO THE BEGINNING, IF IT FAILS? 20 Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with macro syntax | Excel Discussion (Misc queries) | |||
Correct syntax for IF, Then in a macro | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Macro Programming Syntax | Excel Programming | |||
Macro syntax - how to find them | Excel Programming |