Macro help please

[DA]

I am pretty sure this macro was working in a file i had in EXCEL 2003
but does not seem to work in a different file in EXCEL 2007, although
I suspect that the changeover is not the reason. Can someone please
help. Thanks. I am getting a run time error 13: Type Mismatch.on the
line just before Wend. It seems like it is doing one iteration and
then stopping :

Sub CopyPasteCCPInterest()

Dim Difference As Double
Dim CurrentInterestPaymentPaste As Double
Dim CurrentInterestPayment As Double
Difference = 1
While Difference > 0.01
Range("CurrentInterestPaymentPaste").Value = _
Range("CurrentInterestPayment").Value
Application.Calculate
Difference = Abs(Range("CurrentInterestPayment").Value -
Range("CurrentInterestPaymentPaste").Value)
Wend

End Sub

 

[joeu2004]

I am getting a run time error 13: Type Mismatch.

[....]
on the

Sub CopyPasteCCPInterest()
    Dim Difference As Double
    Dim CurrentInterestPaymentPaste As Double
    Dim CurrentInterestPayment As Double
       Difference = 1
    While Difference > 0.01
        Range("CurrentInterestPaymentPaste").Value = _
            Range("CurrentInterestPayment").Value
        Application.Calculate
        Difference = Abs(Range("CurrentInterestPayment").Value -
Range("CurrentInterestPaymentPaste").Value)
    Wend
End Sub

I am suspicious of the correctness of the algorithm. In particular,
it seems odd that you have unused local variables that have has the
same name as defined names in the Excel worksheet.

For example, Range("CurrentInterestPaymentPaste") refers to a defined
name CurrentInterestPaymentPaste in the Excel worksheet, to the local
variable CurrentInterestPaymentPaste.

Another suspicious thing: you assign Range("CurrentInterestPayment")
to Range("CurrentInterestPaymentPaste"), then you expect their
difference to be something other than zero after you calculate.

For that to work, a formula in the cell
Range("CurrentInterestPayment") would have to reference the cell
Range("CurrentInterestPaymentPaste"). That's possible, of course.
But I wonder if it's the case.

I suggest that you start over, posting a more general question.

What are the formulas and cells names or defined names in the Excel
worksheet?

What do you want your VBA algorithm to do? Explain in English and
either math formulas or pseudocode.

If it is possible, would you be amenable to a solution that only
requires Excel formulas, not VBA?

PS: For the algorithm above, you use Do ... Loop While Difference >
0.1. Then you do not need to set Difference=1 initially.

 

[DA]

[....]
 on the

Sub CopyPasteCCPInterest()
    Dim Difference As Double
    Dim CurrentInterestPaymentPaste As Double
    Dim CurrentInterestPayment As Double
       Difference = 1
    While Difference > 0.01
        Range("CurrentInterestPaymentPaste").Value = _
            Range("CurrentInterestPayment").Value
        Application.Calculate
        Difference = Abs(Range("CurrentInterestPayment").Value -
Range("CurrentInterestPaymentPaste").Value)
    Wend
End Sub

I am suspicious of the correctness of the algorithm.  In particular,
it seems odd that you have unused local variables that have has the
same name as defined names in the Excel worksheet.

For example, Range("CurrentInterestPaymentPaste") refers to a defined
name CurrentInterestPaymentPaste in the Excel worksheet, to the local
variable CurrentInterestPaymentPaste.

Another suspicious thing:  you assign Range("CurrentInterestPayment")
to Range("CurrentInterestPaymentPaste"), then you expect their
difference to be something other than zero after you calculate.

For that to work, a formula in the cell
Range("CurrentInterestPayment") would have to reference the cell
Range("CurrentInterestPaymentPaste").  That's possible, of course.
But I wonder if it's the case.

I suggest that you start over, posting a more general question.

What are the formulas and cells names or defined names in the Excel
worksheet?

What do you want your VBA algorithm to do?  Explain in English and
either math formulas or pseudocode.

If it is possible, would you be amenable to a solution that only
requires Excel formulas, not VBA?

PS:  For the algorithm above, you use Do ... Loop While Difference >
0.1.  Then you do not need to set Difference=1 initially.

One of the ranges has code in it and the point of the pasted range is
that it eliminates the need for iterative solving, which i find
dangerous in a large file becasue it will solve through programming
errors that would be circular. So, I don't think a solution with
EXCEL formulas only would work, other than that I could test a cell in
EXCEL rather than this "difference" variable whjihc exits only in
VBA. By the way, for the two range names, they were not previosuly
dimensioned. I added the dimnension when the macro aborted, hoping
that might solve it, but it didn't. In fact, although i am a novice
with VBA, I'm not sure I understand what range.value means when the
range has many cells, e.g.,
Range("CurrentInterestPaymentPaste").Value. If the sum of the cells
in both ranges are the same, is differecne = 0? Or does each cell
need to be the same as the coorespodniong value in the other range?

It does not seem to work in EXCEL 2003 either.

Please give me an example of Loop while difference and assume that the
variable in EXCEL that looks at the differecne is called "CheckSum"

Thanks
Dean

 

[DA]

Ok, no need to respond fuirther.

I simply used the EXCEL spreadsheet variable I had mentioned (whihc is
the differecne between the sum of the two ranges) and it works fine,
i.e.,:

Difference = Range("CheckSumForCCPFacilityInterest").Value

Thanks
Dean

 

[DA]

I'm thinking that the original macro worked only if the range names
referred to only a single cell, rather than a bunch of cells. That
might explain why I recalled that it worked fine before, but did not
now.

Thanks again,
Dean