Cell not calculating after importing data

[Steve]

Hi

I recorded a macro recently which imported some data from a text file, delimited it and set the column data types. One column - the amounts - had the '#' replaced with '£' with the format set to currency, and had all spacesremoved.

The problem is that the cell which gives a complete total of the potential range [=sum($G$5:$G$1500)] is not calculating - the return remains £0.00.

Does anyone have any ideas why the cell isn't updating?

Thanks

Steve

 

[Claus Busch]

Hi Steve,

Am Mon, 4 Feb 2013 06:57:59 -0800 (PST) schrieb Steve:

I recorded a macro recently which imported some data from a text file, delimited it and set the column data types. One column - the amounts - had the '#' replaced with '£' with the format set to currency, and had all spaces removed.

The problem is that the cell which gives a complete total of the potential range [=sum($G$5:$G$1500)] is not calculating - the return remains £0.00.

Calculation = Automatic?
Maybe the cells in column G are still text. Try:

With Range("G5:G1500")
.Replace What:="#", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows
.NumberFormat = "[$£-809]#,##0.00"
End With


Regards
Claus Busch

 

[Steve]

Hi Claus

I had to tweak the your code ever-so-slightly, but it worked a treat; exactly what I need.

Thank you very much!

Best

Steve