I'm using Excel 2003 with all latest updates. When I use the Find & Replace
function, if I click on Find I have three options in Look In -- Formulas,
Values, Comments. If I select Replace I only have the Formula option in Look
In. Can anyone tell me how to fix this. I need to Find and Replace some
values.
Thanks
Steve

Try it and you'll see that values are replaced, too.



vea_ste wrote:

I'm using Excel 2003 with all latest updates. When I use the Find & Replace
function, if I click on Find I have three options in Look In -- Formulas,
Values, Comments. If I select Replace I only have the Formula option in Look
In. Can anyone tell me how to fix this. I need to Find and Replace some
values.
Thanks
Steve

--

Dave Peterson

No it doesn't work that way for me. Everytime I try to replace with the
setting on Formula I get an error message "Formula too long". And I am
unable to replace.
Thanks.

"Dave Peterson" wrote:

Try it and you'll see that values are replaced, too.



vea_ste wrote:

I'm using Excel 2003 with all latest updates. When I use the Find & Replace
function, if I click on Find I have three options in Look In -- Formulas,
Values, Comments. If I select Replace I only have the Formula option in Look
In. Can anyone tell me how to fix this. I need to Find and Replace some
values.
Thanks
Steve

--

Dave Peterson

That's not a problem caused by formulas or values.

That's a problem with the way excel works with long strings in cells. You can
do a small test in a test workbook to see that using formulas works ok when
edit|replace (no long strings, though).

And you could use a macro that tries to do an edit|Replace and for all the cells
that this fails on, the macro can do the work on a cell-by-cell basis:

(Saved from a previous post)

You can use a macro to do the change:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = "$$$$$"
AfterStr = " " 'or chr(10) 'for alt-enter

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

vea_ste wrote:

No it doesn't work that way for me. Everytime I try to replace with the
setting on Formula I get an error message "Formula too long". And I am
unable to replace.
Thanks.

"Dave Peterson" wrote:

Try it and you'll see that values are replaced, too.



vea_ste wrote:

I'm using Excel 2003 with all latest updates. When I use the Find & Replace
function, if I click on Find I have three options in Look In -- Formulas,
Values, Comments. If I select Replace I only have the Formula option in Look
In. Can anyone tell me how to fix this. I need to Find and Replace some
values.
Thanks
Steve

--

Dave Peterson


--

Dave Peterson

Thanks Dave, I am new to macros. I will get back after I test.



"Dave Peterson" wrote:

That's not a problem caused by formulas or values.

That's a problem with the way excel works with long strings in cells. You can
do a small test in a test workbook to see that using formulas works ok when
edit|replace (no long strings, though).

And you could use a macro that tries to do an edit|Replace and for all the cells
that this fails on, the macro can do the work on a cell-by-cell basis:

(Saved from a previous post)

You can use a macro to do the change:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = "$$$$$"
AfterStr = " " 'or chr(10) 'for alt-enter

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

vea_ste wrote:

No it doesn't work that way for me. Everytime I try to replace with the
setting on Formula I get an error message "Formula too long". And I am
unable to replace.
Thanks.

"Dave Peterson" wrote:

Try it and you'll see that values are replaced, too.



vea_ste wrote:

I'm using Excel 2003 with all latest updates. When I use the Find & Replace
function, if I click on Find I have three options in Look In -- Formulas,
Values, Comments. If I select Replace I only have the Formula option in Look
In. Can anyone tell me how to fix this. I need to Find and Replace some
values.
Thanks
Steve

--

Dave Peterson


--

Dave Peterson

Dave, do I need to edit this macro at all to work in my workbook? I copied
it and when I run it nothing happens. I looked at David McRitchie article
and couldn't resolve.
Thanks.

"vea_ste" wrote:

Thanks Dave, I am new to macros. I will get back after I test.



"Dave Peterson" wrote:

That's not a problem caused by formulas or values.

That's a problem with the way excel works with long strings in cells. You can
do a small test in a test workbook to see that using formulas works ok when
edit|replace (no long strings, though).

And you could use a macro that tries to do an edit|Replace and for all the cells
that this fails on, the macro can do the work on a cell-by-cell basis:

(Saved from a previous post)

You can use a macro to do the change:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = "$$$$$"
AfterStr = " " 'or chr(10) 'for alt-enter

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

vea_ste wrote:

No it doesn't work that way for me. Everytime I try to replace with the
setting on Formula I get an error message "Formula too long". And I am
unable to replace.
Thanks.

"Dave Peterson" wrote:

Try it and you'll see that values are replaced, too.



vea_ste wrote:

I'm using Excel 2003 with all latest updates. When I use the Find & Replace
function, if I click on Find I have three options in Look In -- Formulas,
Values, Comments. If I select Replace I only have the Formula option in Look
In. Can anyone tell me how to fix this. I need to Find and Replace some
values.
Thanks
Steve

--

Dave Peterson


--

Dave Peterson

Yes.

This is the what to change string:
BeforeStr = "$$$$$"

This is what to change to string:
AfterStr = " " 'or chr(10) 'for alt-enter

It only works on text values--no formulas. You can change this, but from your
original post, I think that's what you want.

vea_ste wrote:

Dave, do I need to edit this macro at all to work in my workbook? I copied
it and when I run it nothing happens. I looked at David McRitchie article
and couldn't resolve.
Thanks.

"vea_ste" wrote:

Thanks Dave, I am new to macros. I will get back after I test.



"Dave Peterson" wrote:

That's not a problem caused by formulas or values.

That's a problem with the way excel works with long strings in cells. You can
do a small test in a test workbook to see that using formulas works ok when
edit|replace (no long strings, though).

And you could use a macro that tries to do an edit|Replace and for all the cells
that this fails on, the macro can do the work on a cell-by-cell basis:

(Saved from a previous post)

You can use a macro to do the change:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = "$$$$$"
AfterStr = " " 'or chr(10) 'for alt-enter

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

vea_ste wrote:

No it doesn't work that way for me. Everytime I try to replace with the
setting on Formula I get an error message "Formula too long". And I am
unable to replace.
Thanks.

"Dave Peterson" wrote:

Try it and you'll see that values are replaced, too.



vea_ste wrote:

I'm using Excel 2003 with all latest updates. When I use the Find & Replace
function, if I click on Find I have three options in Look In -- Formulas,
Values, Comments. If I select Replace I only have the Formula option in Look
In. Can anyone tell me how to fix this. I need to Find and Replace some
values.
Thanks
Steve

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson