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

On Mar 23, 1:05*pm, DA wrote:
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.

On Mar 23, 1:47*pm, joeu2004 wrote:
On Mar 23, 1:05*pm, DA wrote: 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.

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

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

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