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.
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.