Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell not calculating after importing data
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 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell not calculating after importing data
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell not calculating after importing data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing external data with cell value as input parameter | New Users to Excel | |||
Removing Data Tables formed from importing data from Access | Excel Discussion (Misc queries) | |||
cell format options when importing data | Excel Discussion (Misc queries) | |||
importing time from pdf as table and calculating | Excel Discussion (Misc queries) | |||
Cell formatting when importing data | Excel Discussion (Misc queries) |