Cell not calculating after importing data

S

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
 
C

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
 
S

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
 

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