Selection.Replace doesn't update the cell value in macro!
 

Hi everyone,
I'm trying to read a text file containg data like this
5.55
5.57
5.98
5.63
5.82
into Excel 2000, replacing the "." by ",", and calcualting the mean
value in the following macro sequence:

....
Dim theRange As Range

Range("A1:A5").Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Set theRange = Selection
Range("B1").Value = Application.WorksheetFunction.Average(theRange)
....

I get the run time error 1004 'Could not find Average property for
Worksheet function class'. The problem is probably that after
selection.replace, the cell values aren't correctly updated. Doing this
manually in the worksheet doesn't cause any problem and runs just fine!!!

Any comments, solutions???

Thanks in advance

/Nasser Hosseini

Selection.Replace doesn't update the cell value in macro!
 

How can you average 5,55 and 5,57? Those aren't numbers.
I used your same code to replace the commas with periods
and it worked fine. Sorry for the confusion.

-Brad
-----Original Message-----
Hi everyone,
I'm trying to read a text file containg data like this
5.55
5.57
5.98
5.63
5.82
into Excel 2000, replacing the "." by ",", and
calcualting the mean
value in the following macro sequence:

....
Dim theRange As Range

Range("A1:A5").Select
Selection.Replace What:=".", Replacement:=",",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Set theRange = Selection
Range("B1").Value = Application.WorksheetFunction.Average
(theRange)
....

I get the run time error 1004 'Could not find Average
property for
Worksheet function class'. The problem is probably that
after
selection.replace, the cell values aren't correctly
updated. Doing this
manually in the worksheet doesn't cause any problem and
runs just fine!!!

Any comments, solutions???

Thanks in advance

/Nasser Hosseini

.