PDA

View Full Version : Prob in substitution



abraham30
05-13-2012, 03:13 AM
In the sheet, I have data like this
ibm emp in us=120
csc emp in uk=25
apple emp in russia=0
ibm emp working in bursal=32
chinese emp in us=0
0duncal favour=89
apple 0 in mumbai=89

etc...

Any vba code where ever I will find zero(not zero of 10,120) as for russia, I want to put one word as No cases.i.e.
apple emp in russia=No cases
chinese emp in us=No cases
I have written the macro below but why it is not working

Sub subst()
Range("B1:B65000").Formula = "=Substitute(B1, ""=0"", ""=No cases"")"
End Sub

GTO
05-13-2012, 04:12 AM
Hi there,

You appear to be trying to plunk a formula into a range wherein the the formula refers to the range its in. I'm poop at formulas, but am still pretty sure that ain't happening.

Do you wish to 'fix in place' so-to-speak?

Mark

Bob Phillips
05-13-2012, 04:21 AM
Can't you use Find & Replace to replace =0 with =No cases

Teeroy
05-13-2012, 04:31 AM
Hi Abraham30,

Use the .Find method (with LookAt:=xlPart) for "=0" to identify the cell (Range object) to change. Parse the .value string using the split function (on "=0") then reassemble the 2 parts of the string around "=No cases".

Paul_Hossler
05-13-2012, 06:25 AM
...poop at formulas ... .
Mark


Technical term??? :rofl2:

Paul

GTO
05-14-2012, 05:24 AM
Technical term??? :rofl2:

Paul

Well... at least more gentile than the spontaneous utterences that usually escape my yapper - when trying to figure through a complex (for me) formula of worksheet functions...:dau: