> That's why I suspected it has something to do with Excel not thinking
> the cells have to be re-evaluated when the replace is made in code.
Understood. But my test has demonstrated that Excel does indeed re-evaluate
when the replace is made in code.
Basically, this leaves the locale as the suspect.
Have you tried a replace that removes the spaces and replaces the comma with
a full-stop? It is possible that Excel is placing itself in "Basic
programming" mode where I'd imagine that a full stop is always the decimal
point character (and commas are possibly only known as number separators).
It's certainly worth a try.
Also, don't assume that Excel is using common code to evaluate the cell's
contents when replaced manually or within a program. There is a very good
chance that the code used is entirely separate. And Excel's
locale-independence could well be better developed in the User Interface
code than in the Program Interface code.
(It's important to remember that "recording a macro" is not what you're
actually doing when you "record a macro". What Excel is actually doing is
translating your actions into the nearest equivalent VBA code.)
Regards, Dave S
----- Original Message -----
From: "erikinc99" <erikinc@bredband.
To: <ExcelVBA@yahoogroup
Sent: Tuesday, June 23, 2009 4:56 PM
Subject: [ExcelVBA] Re: Cell value won't update after VB format change
Hi Dave,
Well, quite sure. =)
Locales could possibly explain it, Iäve come across some of those problems
before. The 32-spaces are the special chars for thousands separators (we use
comma as decimal point), so "123456,78" is represented as "123 456,78" in
Excel and other applications.
But, Excel reformats correctly when the replace is done with ctrl+H.
Regardless if I replace with char-32 or "".
I've tried to record as you proposed. When I record it, it works perfectly.
However if I then run the same recorded macro is doesn't work. That's why I
suspected it has something to do with Excel not thinking the cells have to
be re-evaluated when the replace is made in code.
The workaround with manually reading the info and then replacing the value
is of course the way to go if I'm unable to solve the problem. But it's an
awful hack to have to do =)
And it makes it harder to trust the results as Excel won't return a SUM of
such cells as an error, only as 0.
--- In ExcelVBA@yahoogroup
wrote:
>
> > as Excel still won't evalute/reformat the cells if you replace chr 160
> > with "".
> > (Excel recognizes chr 32 as "ordinary spaces" and removes them when
> > reformatting a number ...
>
> Are you sure about this? You're obviously using a different locale than
> I,
> but I suspect my new test is similar. I had cells such as
>
> 1a2a3
> 2aa4
> 100a200a300
>
> and I had my replace statement replace the "a" with chr(32). The result
> is
> that the cells stay in text format, with the spaces separating the digits.
>
> Yet, when I replace those "a" with "", the thing works exactly as I'd want
> it to.
>
> > ... just as it would remove the 0 in 0123,45.)
>
> No, this is a different case. In fact, it will only remove 0 from the
> beginning of the integer or the end of the fractional part. It certainly
> won't remove them from the middle.
>
> Two things occur to me ...
>
> 1. Locale.
>
> Are spaces special in numbers in your locale, similar to the way commas
> are
> special in mine (Australia).
>
> I did another test, where I changed the "a" to commas. This produced
> interesting results. 1a2a3 became 1,2,3 which was treated as text; but
> 100a200a300 became 100,200,300 which Excel treated as a number, formatted
> with comma separators. If you then click on the cell, the formula bar
> shows
> that Excel has removed the commas internally and is just showing them in
> the
> formatted display.
>
> Excel was built around the US locale, so some things are still US-centric.
> This might include the auto-recognition of numbers after replaces. So, a
> double conversion of your comma to full-stop and space to comma might find
> Excel treating it as a number.
>
> 2. Formatting
>
> Make sure that the cells you're working on are not formatted as text. If
> they are, then Excel will leave them as text.
>
> It would certainly be possible to force the cell format to numeric in your
> code. Just record a macro of yourself doing it to get the code right.
>
> You could also change your code to do the cells one by one and do the
> numeric evaluation in the VBA, and fill the cells explicitly. It would be
> slower, but certain.
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "erikinc99" <erikinc@...
> To: <ExcelVBA@yahoogroup
> Sent: Tuesday, June 23, 2009 12:30 AM
> Subject: [ExcelVBA] Re: Cell value won't update after VB format change
>
>
> > Hi Dave,
> >
> > Yes, you are perfectly correct that there is no need to change to chr
> > 32,
> > don't exactly know why I did it that way, unfortunately though this
> > doesn't solve the problem as Excel still won't evalute/reformat the
> > cells
> > if you replace chr 160 with "".
> > (Excel recognizes chr 32 as "ordinary spaces" and removes them when
> > reformatting a number, just as it would remove the 0 in 0123,45.)
> >
> > The problem seems to be that Excel doesn't think that the cells have
> > been
> > changed in a way that may need reformatting, even when you changed the
> > format in VB.
> > If you use replace manually (via Edit -> Replace...) it works both when
> > replacing with chr 32 and blanks.
> >
> > Thanks though!
> > Erik
> >
> > --- In ExcelVBA@yahoogroup
> > wrote:
> >>
> >> I can't see why you're changing the 160's to 32's. Spaces aren't valid
> >> inside numbers. Why not remove them completely?
> >>
> >> I had a couple of cells with spaces (32's) between the digits. These
> >> were
> >> treated as text and flagged as numerics or dates stored as text.
> >>
> >> I then ran the following code:
> >>
> >> Option Explicit
> >>
> >> Private Sub CommandButton1_
> >> Call Range("A1:A3"
> >> End Sub
> >>
> >> This replace removed the spaces and the cells immediately right
> >> justified
> >> as
> >> numbers.
> >>
> >> Regards, Dave S
> >>
> >> ----- Original Message -----
> >> From: "erikinc99" <erikinc@>
> >> To: <ExcelVBA@yahoogroup
> >> Sent: Monday, June 22, 2009 6:02 PM
> >> Subject: [ExcelVBA] Cell value won't update after VB format change
> >>
> >>
> >> > Hi.
> >> >
> >> > I have a problem that occurs when I try to convert a number of cell
> >> > values
> >> > to numbers. Here's the setup:
> >> > I paste an amount of data into Excel from another application, with
> >> > two
> >> > columns, one with a name and another with a value (decimal):
> >> >
> >> > Person1 -1 454 283,00
> >> > Person2 2 491 773,00
> >> >
> >> > Now, unfortunately the application I paste from formats the numbers,
> >> > and
> >> > uses ASCII 160 for the spaces (and not ASCII 32 as usual). Therefore
> >> > Excel
> >> > does not recognize the values as numbers and sets the cell format to
> >> > "General".
> >> >
> >> > The macro I'm writing therefore needs to reformat these to numbers in
> >> > order for everything to work (there are a lot of formulas in the
> >> > workbook
> >> > that sums, adds asf with the numbers). Therefore I wrote the code:
> >> >
> >> > .Range("B3:B1000"
> >> > .Range("B3:B1000"
> >> >
> >> > Now, Excel kinda recognizes the values as numbers as it places the
> >> > little
> >> > green info tabs on all cells saying it's "Number formatted as text".
> >> > But
> >> > it still doesn't make the cells "calculate-able"
> >> >
> >> > The thing is, this won't happen if you do a manual replace, then
> >> > Excel
> >> > reformats them automatically. If you, after the macro, enter a cell
> >> > (by
> >> > double clicking it or pressing F2) end then hit Enter the cell gets
> >> > formatted correctly. So, after the macro has replaced and changed
> >> > format,
> >> > I tried to copy a blank cell, and then paste special it with "values"
> >> > and
> >> > "add" checked, in order for it to reformat them, which works. However
> >> > if
> >> > you try to automate this to, i.e. writing this in the code also:
> >> >
> >> > .Range("IV1"
> >> > .Range("B3:B1000"
> >> > Operation:=xlAdd
> >> >
> >> > Then Excel still won't reformat them. A Calculate call won't fix it
> >> > either. So...the problem is, Excel don't reformat data when you do a
> >> > Replace call in VB, or when you paste special in VB...
> >> >
> >> > Anyone have a solution? =)
> >> >
> >> > Kind regards
> >> > Erik
> >> >
> >> >
> >> >
> >> > ------------
> >> >
> >> > ------------
> >> > Be sure to check out TechTrax Ezine for many, free Excel VBA
> >> > articles!
> >> > Go
> >> > here: http://www.mousetra
> >> > search
> >> > the ARCHIVES for EXCEL VBA.
> >> >
> >> > ------------
> >> > Visit our ExcelVBA group home page for more info and support files:
> >> > http://groups.
> >> >
> >> > ------------
> >> > More free tutorials and resources available at:
> >> > http://www.mousetra
> >> >
> >> > ------------
> >> >
> >> >
> >> >
> >> >
> >>
> >
> >
> >
> >
> > ------------
> >
> > ------------
> > Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
> > Go
> > here: http://www.mousetra
> > the ARCHIVES for EXCEL VBA.
> >
> > ------------
> > Visit our ExcelVBA group home page for more info and support files:
> > http://groups.
> >
> > ------------
> > More free tutorials and resources available at:
> > http://www.mousetra
> >
> > ------------
> >
> >
> >
>
>
> ------------
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.339 / Virus Database: 270.12.87/2195 - Release Date: 06/22/09
> 06:54:00
>
------------
------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetra
ARCHIVES for EXCEL VBA.
------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.
------------
More free tutorials and resources available at:
http://www.mousetra
------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
0 comments:
Post a Comment