Making A Nacro To Change The Contents Of Many Cells
 

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

Making A Nacro To Change The Contents Of Many Cells
 

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

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

Making A Nacro To Change The Contents Of Many Cells
 

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



On Thu, 6 May 2004 18:41:05 -0700, Edwin Tam
wrote:

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

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

Making A Nacro To Change The Contents Of Many Cells
 

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

--
Message posted from http://www.ExcelForum.com

Making A Nacro To Change The Contents Of Many Cells
 

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


On Thu, 6 May 2004 23:06:19 -0500, mudraker
wrote:

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 Sub


---
Message posted from http://www.ExcelForum.com/