Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Find and replace - problem with automatically changing formatting
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
jwa90010
 
Posts: n/a
Default Find and replace - problem with automatically changing formatting
Hi,

I'm having a problem with "find and replace" With Italics. If my cell is a
mixture of italics and regular font text and I do a find and replace of a
single word, it automatically changes the formatting to all italics. Does
anyone have a solution for these problems? I have searched HELP and I have
searched the Microsoft Excel and Microsoft Office web sites. NOTHING is
discussed on this formatting problem. HELPPPP!!!! Thanks.
Reply With QuoteReply With Quote
  #2   Report Post  
Rowan
 
Posts: n/a
Default
Save your file and then try this find and replace macro.
The macro checks the first character of the word/phrase to be changed. If
this is in italics then the new word/phrase will be in italics. All other
text is kept in it's original format with respect to italics.

Sub RepItal()

Dim i As Integer
Dim tLen As Integer
Dim fChar As Integer
Dim fLen As Integer
Dim rLen As Integer
Dim ital() As Boolean
Dim newItal() As Boolean
Dim isItal As Boolean
Dim fVal As String
Dim rVal As String
Dim fNext As Boolean
Dim fCell As Range

fNext = True

fVal = InputBox("Find...")
rVal = InputBox("Replace with...")

Do
If Selection.Count = 1 Then

Set fCell = Cells.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
Else
Set fCell = Selection.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
End If

If Not fCell Is Nothing Then

tLen = Len(fCell.Value)
ReDim ital(tLen - 1) As Boolean
For i = 1 To tLen
ital(i - 1) = fCell.Characters(i, 1).Font.Italic
Next i

fLen = Len(fVal)
rLen = Len(rVal)
fChar = InStr(1, fCell.Value, fVal)
isItal = ital(fChar)
tLen = tLen + (rLen - fLen)
ReDim newItal(tLen - 1) As Boolean
For i = 1 To fChar - 1
newItal(i - 1) = ital(i - 1)
Next i
For i = fChar To (fChar + rLen) - 1
newItal(i - 1) = isItal
Next i
For i = fChar + rLen To UBound(newItal)
newItal(i) = ital(i - (rLen - fLen))
Next i

fCell.Replace What:=fVal, Replacement:=rVal

For i = 1 To UBound(newItal)
fCell.Characters(i, 1).Font.Italic = newItal(i - 1)
Next i
Else
fNext = False
End If

If fNext = True Then
If MsgBox("Continue search?", vbYesNo) = vbNo _
Then fNext = False
End If
Loop While fNext = True

End Sub

Hope this helps
Rowan

"jwa90010" wrote:

Hi,

I'm having a problem with "find and replace" With Italics. If my cell is a
mixture of italics and regular font text and I do a find and replace of a
single word, it automatically changes the formatting to all italics. Does
anyone have a solution for these problems? I have searched HELP and I have
searched the Microsoft Excel and Microsoft Office web sites. NOTHING is
discussed on this formatting problem. HELPPPP!!!! Thanks.
Reply With QuoteReply With Quote
  #3   Report Post  
Rowan
 
Posts: n/a
Default
PS This is case sensitive

"Rowan" wrote:

Save your file and then try this find and replace macro.
The macro checks the first character of the word/phrase to be changed. If
this is in italics then the new word/phrase will be in italics. All other
text is kept in it's original format with respect to italics.

Sub RepItal()

Dim i As Integer
Dim tLen As Integer
Dim fChar As Integer
Dim fLen As Integer
Dim rLen As Integer
Dim ital() As Boolean
Dim newItal() As Boolean
Dim isItal As Boolean
Dim fVal As String
Dim rVal As String
Dim fNext As Boolean
Dim fCell As Range

fNext = True

fVal = InputBox("Find...")
rVal = InputBox("Replace with...")

Do
If Selection.Count = 1 Then

Set fCell = Cells.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
Else
Set fCell = Selection.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
End If

If Not fCell Is Nothing Then

tLen = Len(fCell.Value)
ReDim ital(tLen - 1) As Boolean
For i = 1 To tLen
ital(i - 1) = fCell.Characters(i, 1).Font.Italic
Next i

fLen = Len(fVal)
rLen = Len(rVal)
fChar = InStr(1, fCell.Value, fVal)
isItal = ital(fChar)
tLen = tLen + (rLen - fLen)
ReDim newItal(tLen - 1) As Boolean
For i = 1 To fChar - 1
newItal(i - 1) = ital(i - 1)
Next i
For i = fChar To (fChar + rLen) - 1
newItal(i - 1) = isItal
Next i
For i = fChar + rLen To UBound(newItal)
newItal(i) = ital(i - (rLen - fLen))
Next i

fCell.Replace What:=fVal, Replacement:=rVal

For i = 1 To UBound(newItal)
fCell.Characters(i, 1).Font.Italic = newItal(i - 1)
Next i
Else
fNext = False
End If

If fNext = True Then
If MsgBox("Continue search?", vbYesNo) = vbNo _
Then fNext = False
End If
Loop While fNext = True

End Sub

Hope this helps
Rowan

"jwa90010" wrote:

Hi,

I'm having a problem with "find and replace" With Italics. If my cell is a
mixture of italics and regular font text and I do a find and replace of a
single word, it automatically changes the formatting to all italics. Does
anyone have a solution for these problems? I have searched HELP and I have
searched the Microsoft Excel and Microsoft Office web sites. NOTHING is
discussed on this formatting problem. HELPPPP!!!! Thanks.
Reply With QuoteReply With Quote
  #4   Report Post  
jwa90010
 
Posts: n/a
Default
Seems macros are the only way to go. Will learn macros now. Thanks. Joy

"Rowan" wrote:

PS This is case sensitive

"Rowan" wrote:

Save your file and then try this find and replace macro.
The macro checks the first character of the word/phrase to be changed. If
this is in italics then the new word/phrase will be in italics. All other
text is kept in it's original format with respect to italics.

Sub RepItal()

Dim i As Integer
Dim tLen As Integer
Dim fChar As Integer
Dim fLen As Integer
Dim rLen As Integer
Dim ital() As Boolean
Dim newItal() As Boolean
Dim isItal As Boolean
Dim fVal As String
Dim rVal As String
Dim fNext As Boolean
Dim fCell As Range

fNext = True

fVal = InputBox("Find...")
rVal = InputBox("Replace with...")

Do
If Selection.Count = 1 Then

Set fCell = Cells.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
Else
Set fCell = Selection.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
End If

If Not fCell Is Nothing Then

tLen = Len(fCell.Value)
ReDim ital(tLen - 1) As Boolean
For i = 1 To tLen
ital(i - 1) = fCell.Characters(i, 1).Font.Italic
Next i

fLen = Len(fVal)
rLen = Len(rVal)
fChar = InStr(1, fCell.Value, fVal)
isItal = ital(fChar)
tLen = tLen + (rLen - fLen)
ReDim newItal(tLen - 1) As Boolean
For i = 1 To fChar - 1
newItal(i - 1) = ital(i - 1)
Next i
For i = fChar To (fChar + rLen) - 1
newItal(i - 1) = isItal
Next i
For i = fChar + rLen To UBound(newItal)
newItal(i) = ital(i - (rLen - fLen))
Next i

fCell.Replace What:=fVal, Replacement:=rVal

For i = 1 To UBound(newItal)
fCell.Characters(i, 1).Font.Italic = newItal(i - 1)
Next i
Else
fNext = False
End If

If fNext = True Then
If MsgBox("Continue search?", vbYesNo) = vbNo _
Then fNext = False
End If
Loop While fNext = True

End Sub

Hope this helps
Rowan

"jwa90010" wrote:

Hi,

I'm having a problem with "find and replace" With Italics. If my cell is a
mixture of italics and regular font text and I do a find and replace of a
single word, it automatically changes the formatting to all italics. Does
anyone have a solution for these problems? I have searched HELP and I have
searched the Microsoft Excel and Microsoft Office web sites. NOTHING is
discussed on this formatting problem. HELPPPP!!!! Thanks.
Reply With QuoteReply With Quote
  #5   Report Post  
Jack Sons
 
Posts: n/a
Default
Rowan,

Nice code, but when I tried it was absolutely not case sensitive. Did I miss
something?

Jack Sons
The Netherlands


"Rowan" schreef in bericht
...
PS This is case sensitive

"Rowan" wrote:

Save your file and then try this find and replace macro.
The macro checks the first character of the word/phrase to be changed. If
this is in italics then the new word/phrase will be in italics. All other
text is kept in it's original format with respect to italics.

Sub RepItal()

Dim i As Integer
Dim tLen As Integer
Dim fChar As Integer
Dim fLen As Integer
Dim rLen As Integer
Dim ital() As Boolean
Dim newItal() As Boolean
Dim isItal As Boolean
Dim fVal As String
Dim rVal As String
Dim fNext As Boolean
Dim fCell As Range

fNext = True

fVal = InputBox("Find...")
rVal = InputBox("Replace with...")

Do
If Selection.Count = 1 Then

Set fCell = Cells.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
Else
Set fCell = Selection.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
End If

If Not fCell Is Nothing Then

tLen = Len(fCell.Value)
ReDim ital(tLen - 1) As Boolean
For i = 1 To tLen
ital(i - 1) = fCell.Characters(i, 1).Font.Italic
Next i

fLen = Len(fVal)
rLen = Len(rVal)
fChar = InStr(1, fCell.Value, fVal)
isItal = ital(fChar)
tLen = tLen + (rLen - fLen)
ReDim newItal(tLen - 1) As Boolean
For i = 1 To fChar - 1
newItal(i - 1) = ital(i - 1)
Next i
For i = fChar To (fChar + rLen) - 1
newItal(i - 1) = isItal
Next i
For i = fChar + rLen To UBound(newItal)
newItal(i) = ital(i - (rLen - fLen))
Next i

fCell.Replace What:=fVal, Replacement:=rVal

For i = 1 To UBound(newItal)
fCell.Characters(i, 1).Font.Italic = newItal(i - 1)
Next i
Else
fNext = False
End If

If fNext = True Then
If MsgBox("Continue search?", vbYesNo) = vbNo _
Then fNext = False
End If
Loop While fNext = True

End Sub

Hope this helps
Rowan

"jwa90010" wrote:

Hi,

I'm having a problem with "find and replace" With Italics. If my cell
is a
mixture of italics and regular font text and I do a find and replace of
a
single word, it automatically changes the formatting to all italics.
Does
anyone have a solution for these problems? I have searched HELP and I
have
searched the Microsoft Excel and Microsoft Office web sites. NOTHING
is
discussed on this formatting problem. HELPPPP!!!! Thanks.


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5
Default Find and replace - problem with automatically changing formatt
Does this work to change from "normal" font to italics?

"Rowan" wrote:

Save your file and then try this find and replace macro.
The macro checks the first character of the word/phrase to be changed. If
this is in italics then the new word/phrase will be in italics. All other
text is kept in it's original format with respect to italics.

Sub RepItal()

Dim i As Integer
Dim tLen As Integer
Dim fChar As Integer
Dim fLen As Integer
Dim rLen As Integer
Dim ital() As Boolean
Dim newItal() As Boolean
Dim isItal As Boolean
Dim fVal As String
Dim rVal As String
Dim fNext As Boolean
Dim fCell As Range

fNext = True

fVal = InputBox("Find...")
rVal = InputBox("Replace with...")

Do
If Selection.Count = 1 Then

Set fCell = Cells.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
Else
Set fCell = Selection.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
End If

If Not fCell Is Nothing Then

tLen = Len(fCell.Value)
ReDim ital(tLen - 1) As Boolean
For i = 1 To tLen
ital(i - 1) = fCell.Characters(i, 1).Font.Italic
Next i

fLen = Len(fVal)
rLen = Len(rVal)
fChar = InStr(1, fCell.Value, fVal)
isItal = ital(fChar)
tLen = tLen + (rLen - fLen)
ReDim newItal(tLen - 1) As Boolean
For i = 1 To fChar - 1
newItal(i - 1) = ital(i - 1)
Next i
For i = fChar To (fChar + rLen) - 1
newItal(i - 1) = isItal
Next i
For i = fChar + rLen To UBound(newItal)
newItal(i) = ital(i - (rLen - fLen))
Next i

fCell.Replace What:=fVal, Replacement:=rVal

For i = 1 To UBound(newItal)
fCell.Characters(i, 1).Font.Italic = newItal(i - 1)
Next i
Else
fNext = False
End If

If fNext = True Then
If MsgBox("Continue search?", vbYesNo) = vbNo _
Then fNext = False
End If
Loop While fNext = True

End Sub

Hope this helps
Rowan

"jwa90010" wrote:

Hi,

I'm having a problem with "find and replace" With Italics. If my cell is a
mixture of italics and regular font text and I do a find and replace of a
single word, it automatically changes the formatting to all italics. Does
anyone have a solution for these problems? I have searched HELP and I have
searched the Microsoft Excel and Microsoft Office web sites. NOTHING is
discussed on this formatting problem. HELPPPP!!!! Thanks.
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Find and replace - problem with automatically changing formatt
You have a response at your other post.

Stephanie M wrote:

Does this work to change from "normal" font to italics?

"Rowan" wrote:

Save your file and then try this find and replace macro.
The macro checks the first character of the word/phrase to be changed. If
this is in italics then the new word/phrase will be in italics. All other
text is kept in it's original format with respect to italics.

Sub RepItal()

Dim i As Integer
Dim tLen As Integer
Dim fChar As Integer
Dim fLen As Integer
Dim rLen As Integer
Dim ital() As Boolean
Dim newItal() As Boolean
Dim isItal As Boolean
Dim fVal As String
Dim rVal As String
Dim fNext As Boolean
Dim fCell As Range

fNext = True

fVal = InputBox("Find...")
rVal = InputBox("Replace with...")

Do
If Selection.Count = 1 Then

Set fCell = Cells.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
Else
Set fCell = Selection.Find(What:=fVal, LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=True)
End If

If Not fCell Is Nothing Then

tLen = Len(fCell.Value)
ReDim ital(tLen - 1) As Boolean
For i = 1 To tLen
ital(i - 1) = fCell.Characters(i, 1).Font.Italic
Next i

fLen = Len(fVal)
rLen = Len(rVal)
fChar = InStr(1, fCell.Value, fVal)
isItal = ital(fChar)
tLen = tLen + (rLen - fLen)
ReDim newItal(tLen - 1) As Boolean
For i = 1 To fChar - 1
newItal(i - 1) = ital(i - 1)
Next i
For i = fChar To (fChar + rLen) - 1
newItal(i - 1) = isItal
Next i
For i = fChar + rLen To UBound(newItal)
newItal(i) = ital(i - (rLen - fLen))
Next i

fCell.Replace What:=fVal, Replacement:=rVal

For i = 1 To UBound(newItal)
fCell.Characters(i, 1).Font.Italic = newItal(i - 1)
Next i
Else
fNext = False
End If

If fNext = True Then
If MsgBox("Continue search?", vbYesNo) = vbNo _
Then fNext = False
End If
Loop While fNext = True

End Sub

Hope this helps
Rowan

"jwa90010" wrote:

Hi,

I'm having a problem with "find and replace" With Italics. If my cell is a
mixture of italics and regular font text and I do a find and replace of a
single word, it automatically changes the formatting to all italics. Does
anyone have a solution for these problems? I have searched HELP and I have
searched the Microsoft Excel and Microsoft Office web sites. NOTHING is
discussed on this formatting problem. HELPPPP!!!! Thanks.

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
find and replace steve Excel Discussion (Misc queries) 1 June 23rd 05 01:43 AM
Find and REPLACE within a selection, or column- not entire sheet/. smithers2002 Excel Worksheet Functions 4 April 21st 05 04:45 PM
Find and Replace - Quickest Option? Lindsey M Excel Worksheet Functions 1 March 8th 05 11:34 AM
find replace cursor default to find box luffa Excel Discussion (Misc queries) 0 February 3rd 05 12:11 AM
VB Find and Replace Bony_Pony Excel Worksheet Functions 10 December 6th 04 05:45 PM


All times are GMT +1. The time now is 09:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement