vba - Replace function converts text format to number format. Is it excel bug? -

-

i'm not newbie in excel or googling, behavior surprising/illogical:

  1. format column "a" text
  2. write numbers cells, e.g. 74 a1, a2 , a3 - formatted text, not sum up, looks fine expected
  3. go find - replace 74 75
  4. after replace 75's sum up, behaving numbers, when cells still formatted text! can't why?!

this "bug" (?) causing me troubles in vba, because following code not convert data expected results ('numbers' in text format) , can't text format:

selection.replace what:="74", replacement:="75"

same result when try of these methods:

selection.replace what:="74", replacement:=format("75", "@")  selection.replace what:="74", replacement:=cstr("75")  selection.replace what:="74", replacement:="75" selection.numberformat = "@"

only solution works this:

selection.replace what:="74", replacement:="'75"

but don't want apostroph in cell ...

is there other way how replace text, number in cell - text, number? feel dummy spend hour thinking such basics!

edit: method usage of named range gives me 75's numbers (cell format text):

selection.replace what:="74", replacement:=range("number_75_in_text_format").text

you need describe how getting this. try running either of these:

sub bugdemo() activecell     .clear     .numberformat = "@"     .value = "101"     .replace what:="0", replacement:="7" end end sub  sub bugdemo2() activecell     .clear     .numberformat = "@"     .value = "101"     .replace what:="101", replacement:="7" end end sub

after replacement, values remain text


Comments

Post a Comment

Popular posts from this blog

javascript - Count length of each class -

-
i have below code in this fiddle . html <span class="myname">john smith</span> <span class="myname">john smith</span> <span class="myname">john smith</span> <span class="myname">john smith</span> <span class="myname">john smith</span> <span class="myname">john smith</span> javascript $('.myname').each(function(){ var text = $(this).html().split(' '), len = text.length, test = $(this).val().length, result = []; console.log('outside: ' + test); for( var = 0; < len; i++ ) { result[i] = '<span class="name-'+[i]+'" >' + text[i] + '</span>'; console.log('inside: ' + test); } $(this).html(result.join(' ')); console.log('after
Read more

What design pattern is this code in Javascript? -

-
this question has answer here: why write “.call(this)” @ end of javascript anonymous function? [duplicate] 4 answers what kind of design pattern , significance of using closure? (function(){ // code here }).call(this); edit then difference between above code , following this keyword still refer same object in both ways. (function(){ // code here })(); thats invoked function expression. more info here: http://benalman.com/news/2010/11/immediately-invoked-function-expression/ the purpose run code while protecting scope (so variables declared within don't leak out global scope. update call sets value of this function being applied on. without it, value set window object, there set outer scope.
Read more

hadoop - Restrict secondarynamenode to be installed and run on any other node in the cluster -

-
i have have cloudera cdh3u4 cluster setup. have secondarynamenode daemon running on 1 of nodes. i have ensure user has root access node on cluster (for example, datanode not have other daemons running) should not able start instance of secondarynamenode daemon on node (thereby having 2 secondarynamenodes on cluster) how can achieve this? thanks in advance. add property hdfs-site.xml namedfs.secondary.http.address valuesecondarynamenode-host address or ip:50090
Read more