Modifying many cells with find/replace without losing unique data. pls help

[Ty Williams]

Hi,

Perhaps it is easier to explain what I am trying to do...


IF('New Look Nil Reports'!B43=1,"NIL","NON")

Basically the above is how it is at present, checking if a sheet has a
one in B43.

I need to do the following

=+IF(Z7>=1," ",IF('New Look Nil Reports'!B43=1,"NIL","NON"))

Whereas the Z7 relates to the cell to the left; this will then show a
blank (a space actually) in the cell, rather than the NIL or NON
label.

My problem is that the B43 section is not sequential, so I cannot use
a fill; the next line may be B12 or B65.

So I will need to find and replace

+IF(
with
+IF(LEFTCELL>=1,"",IF(

Where LEFTCELL relates to the command/function to check the cell to
the left.

and I will need to add a close parenthesis at the end.....

Otherwise I will have to enter it all manually, and CRTL/SHIFT+ENTER
for the curly brackets as it is a array...

I am learning slowly, but this eludes me..

Many thanks

Ty

 

[Bernie Deitrick]

Ty,

The difficulty is in balancing the parentheses. The best way is to use VBA.

Copy the macro below, place it in a codemodule, select the cells with the
formulas to be modified, then run the macro.

HTH,
Bernie
MS Excel MVP

Sub FixTysFormulas()
Dim myCell As Range
Dim myForm As String

For Each myCell In Selection
myForm = myCell.Formula
myForm = Replace(myForm, "=", _
"=IF(Z" & myCell.Row & ">1,"" "",", 1, 1) & ")"
myCell.Formula = myForm
Next myCell
End Sub

 

[Bernie Deitrick]

As Homer would say, "D'Oh!"

I'm sorry. I re-read your post, and realized that you probably want:

Sub FixTysFormulas()
Dim myCell As Range
Dim myForm As String

For Each myCell In Selection
myForm = myCell.Formula
myForm = Replace(myForm, "=", _
"=IF(" & myCell(1,0).Address(False,False) & ">1,"" "",", 1, 1) & ")"
myCell.Formula = myForm
Next myCell
End Sub

Still not sure why you expect to array enter this, though....

HTH,
Bernie
MS Excel MVP

 

[Ty Williams]

Thank you thank you thank you!

Apart from the very slight modification of >=1 the macro worked a
treat!

I thought it needed to be entered as an array with the curly brackets
{} cos the other cell it relates to is

{=COUNT(IF('Data Entry Complaints'!$N$2:$N$799=Array!$A60,IF('Data
Entry Complaints'!$B$2:$B$799=4,0)))} which checks an area against a
list, and also if in this case the entry was in April...

Many thanks again.

Ty