Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing external data with cell value as input parameter PADS New Users to Excel 0 October 2nd 08 10:45 PM
Removing Data Tables formed from importing data from Access Andrea Jones Excel Discussion (Misc queries) 0 April 10th 08 12:01 PM
cell format options when importing data t_coop Excel Discussion (Misc queries) 2 April 7th 08 03:12 PM
importing time from pdf as table and calculating ludikris Excel Discussion (Misc queries) 5 April 12th 06 08:58 AM
Cell formatting when importing data cjay Excel Discussion (Misc queries) 2 May 18th 05 10:01 PM


All times are GMT +1. The time now is 07:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"