Evaluate does not work

S

Slim Slender

numRows = Selection.CurrentRegion.Rows.Count
myRow = 2 'start below header
Do
For myColumn = 4 To 11 '
With Worksheets("Sheet1")
myArray(1) = .Cells(myRow, 1)
Age = .Cells(myRow, 2)
myArray(2) =
Evaluate("=IF(TRUNC(Age)=0,"""",TRUNC(Age)&"" y,"")&"" "" &ROUND(((Age-
TRUNC(Age))*365)/30,0)&"" mo.""")
myArray(3) = .Cells(myRow, 3)
End With
With Worksheets("Sheet2")
i = i + 1
.Range(.Cells(i, 1), .Cells(i, 3)) = myArray
End With
Next myColumn
myRow = myRow + 1
Loop Until Cells(myRow, 1).Row > numRows

The code above runs fine and works in every way reading some junk off
one sheet and writing it to another. The only problem is the
myArray(2) = Evaluate. On the first pass, Age = 37.05 and myArray(2)
correctly evaluates to 37 y, 1 mo. In the next loop Age changes to
17.91 but myArray(2) stays 37 y, 1 mo. and remains that from then on
regardless of what Age is.
 

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

Top