Making A Nacro To Change The Contents Of Many Cells

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Greetings,

I need to add an If statement to about 100 cells in a row. The cells
contain links to another part of the sheet. Most of the source cells
have formula which produces a "0". I am trying to replace the "0"
with a "". the formula is simple:

=If([Linked Cell]<>0,[Linked Cell],"")

Right now I have:

=+[Linked Cell]

I can change it with a series of keystrokes. I tried to make a macro
and that does not work. Macros used to be keystroke recorders, but
not any more. (Sigh)

Is there anyway to make this more automatic?

If there is a way, how many ways are there, anyone know?

TIA

-Minitman
 
You don't need a macro.
You just need to use the Find-Replace feature of Excel.

- Select the cells you want to process.
- From the Edit menu, choose Replace
- In the Find What box, enter <>0
- In the Replace with box, enter <>""
- Click Replace All

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com


----- Minitman wrote: -----

Greetings,

I need to add an If statement to about 100 cells in a row. The cells
contain links to another part of the sheet. Most of the source cells
have formula which produces a "0". I am trying to replace the "0"
with a "". the formula is simple:

=If([Linked Cell]<>0,[Linked Cell],"")

Right now I have:

=+[Linked Cell]

I can change it with a series of keystrokes. I tried to make a macro
and that does not work. Macros used to be keystroke recorders, but
not any more. (Sigh)

Is there anyway to make this more automatic?

If there is a way, how many ways are there, anyone know?

TIA

-Minitman
 
Hey Edwin,

I think you misread my question, The formu;aa that I have NOW is:

=+[Linked Cell] (the term [Linked Cell] is not the term in
the formula, it represents what is actually in each cell and what is
in each cell is different))

What I am trying to get to is:

=If([Linked Cell]<>0,[Linked Cell],"")

As you can see, the only items that can be replace because they are in
every cell is the "=+" with "=If(", which is not nearly enough.

Thanks for the reply.

Anyone else want to weigh in on this one?

TIA

-Minitman
 
Try





Sub ff()

Dim c As Range
Dim sTxt As String

For Each c In Range("a1:a10")
If c.Value <> "" Then
Stop
sTxt$ = Mid(c.Formula, 1)
sTxt$ = Mid(sTxt, 2)
c.Value = "=If(" & sTxt & "<>0," & sTxt & ","""")"

End If
Next c

End Su
 
Hey Mudraker,

That is what I was looking for. With that I was able to finish the
conversion that was taking forever.

Thank you very much.

-Minitman
 
Back
Top