Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro Help - Bolding info
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Macro Help - Bolding info

Hello,

The macro bellow was written to find and replace text within a work
sheet. I also need it to bold those replacements.

Can anyone help?

Thank you,
ALC :)

Macro-

Sub Replace()
'
' Replace Macro
' Macro recorded 7/27/2005 by jthomas
'
' Keyboard Shortcut: Ctrl+z
'ActiveSheet.Select

Cells.Replace What:="RED VINES BLACK LICORICE LICORICE TWIST 64OZ
4136400103" _
, Replacement:="RED VINES JAR BLACK LICORICE TWIST 64OZ
4136400103", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 64OZ
4136400104", _
Replacement:="RED VINES JAR RED TWIST 64OZ 4136400104",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 2OZ
4136400222", _
Replacement:="RED VINES RED TWIST 2OZ 4136400222",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES BLACK LICORICE LICORICE TWIST 8OZ
4136400231", _
Replacement:="RED VINES BLACK LICORICE TWIST 8OZ 4136400231",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 8OZ
4136400232", _
Replacement:="RED VINES RED TWIST 8OZ 4136400232",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES ASSORTED LICORICE LICORICE BITE 8OZ
4136400240", _
Replacement:="RED VINES MIXED BITES 8OZ 4136400240",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES GRAPE LICORICE TWIST 8OZ 4136400246", _
Replacement:="RED VINES GRAPE TWIST 8OZ 4136400246",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES BLUE RASPBERRY LICORICE TWIST 8OZ
4136400247", _
Replacement:="RED VINES BLUE RASPBERRY TWIST 8OZ 4136400247",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES WATERMELON LICORICE TWIST 8OZ
4136400248", _
Replacement:="RED VINES WATERMELON TWIST 8OZ 4136400248",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES STRAWBERRY LICORICE TWIST 16OZ
4136400251", _
Replacement:="RED VINES JUMBO STRAWBERRY TWIST 16OZ
4136400251", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES CHERRY LICORICE TWIST 16OZ 4136400255",
_
Replacement:="RED VINES JUMBO CHERRY TWIST 16OZ 4136400255",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES STRAWBERRY LICORICE TWIST 5.5OZ
4136400271", _
Replacement:="RED VINES TRAY STRAWBERRY TWIST 5.5OZ
4136400271", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 6.5OZ
4136400273", _
Replacement:="RED VINES RED TWIST 6.5OZ 4136400273",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES CHERRY LICORICE TWIST 5.5OZ 4136400275",
_
Replacement:="RED VINES TRAY CHERRY TWIST 5.5OZ 4136400275",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub


--
ALC
------------------------------------------------------------------------
ALC's Profile: http://www.excelforum.com/member.php...o&userid=25360
View this thread: http://www.excelforum.com/showthread...hreadid=394032

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Macro Help - Bolding info
Are you bolding the whole cell? (It's a lot easier if you say yes!)

This worked ok for me:

Option Explicit
Sub DoReplace()

Dim myLookFor As Variant
Dim myReplacements As Variant
Dim wks As Worksheet
Dim FoundCell As Range
Dim iCtr As Long
Dim FirstAddress As String

myLookFor = Array("RED VINES BLACK LICORICE LICORICE TWIST 64OZ 4136400103", _
"RED VINES RED LICORICE LICORICE TWIST 64OZ 4136400104", _
"RED VINES RED LICORICE LICORICE TWIST 2OZ 4136400222", _
"RED VINES BLACK LICORICE LICORICE TWIST 8OZ 4136400231", _
"RED VINES RED LICORICE LICORICE TWIST 8OZ 4136400232", _
"RED VINES ASSORTED LICORICE LICORICE BITE 8OZ 4136400240", _
"RED VINES GRAPE LICORICE TWIST 8OZ 4136400246", _
"RED VINES BLUE RASPBERRY LICORICE TWIST 8OZ 4136400247", _
"RED VINES WATERMELON LICORICE TWIST 8OZ 4136400248", _
"RED VINES STRAWBERRY LICORICE TWIST 16OZ 4136400251", _
"RED VINES CHERRY LICORICE TWIST 16OZ 4136400255", _
"RED VINES STRAWBERRY LICORICE TWIST 5.5OZ 4136400271", _
"RED VINES RED LICORICE LICORICE TWIST 6.5OZ 4136400273", _
"RED VINES CHERRY LICORICE TWIST 5.5OZ 4136400275")


myReplacements = Array("RED VINES JAR BLACK LICORICE TWIST 64OZ 4136400103", _
"RED VINES JAR RED TWIST 64OZ 4136400104", _
"RED VINES RED TWIST 2OZ 4136400222", _
"RED VINES BLACK LICORICE TWIST 8OZ 4136400231", _
"RED VINES RED TWIST 8OZ 4136400232", _
"RED VINES MIXED BITES 8OZ 4136400240", _
"RED VINES GRAPE TWIST 8OZ 4136400246", _
"RED VINES BLUE RASPBERRY TWIST 8OZ 4136400247", _
"RED VINES WATERMELON TWIST 8OZ 4136400248", _
"RED VINES JUMBO STRAWBERRY TWIST 16OZ 4136400255", _
"RED VINES JUMBO CHERRY TWIST 16OZ 4136400255", _
"RED VINES TRAY STRAWBERRY TWIST 5.5OZ 4136400271", _
"RED VINES RED TWIST 6.5OZ 4136400273", _
"RED VINES TRAY CHERRY TWIST 5.5OZ 4136400275")

If UBound(myLookFor) < UBound(myReplacements) Then
MsgBox "Design error! Not same number of elements!"
Exit Sub
End If

Set wks = ActiveSheet

With wks

For iCtr = LBound(myLookFor) To UBound(myLookFor)
Set FoundCell = .Cells.Find(What:=myLookFor(iCtr), _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlNext, searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address

Do
FoundCell.Font.Bold = True
Set FoundCell = .Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress

.Cells.Replace What:=myLookFor(iCtr), _
Replacement:=myReplacements(iCtr), _
MatchCase:=False, SearchOrder:=xlNext
End If
Next iCtr

End With

End Sub

It actually finds all the strings and bolds those cells. Then does the
replacement.





ALC wrote:

Hello,

The macro bellow was written to find and replace text within a work
sheet. I also need it to bold those replacements.

Can anyone help?

Thank you,
ALC :)

Macro-

Sub Replace()
'
' Replace Macro
' Macro recorded 7/27/2005 by jthomas
'
' Keyboard Shortcut: Ctrl+z
'ActiveSheet.Select

Cells.Replace What:="RED VINES BLACK LICORICE LICORICE TWIST 64OZ
4136400103" _
, Replacement:="RED VINES JAR BLACK LICORICE TWIST 64OZ
4136400103", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 64OZ
4136400104", _
Replacement:="RED VINES JAR RED TWIST 64OZ 4136400104",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 2OZ
4136400222", _
Replacement:="RED VINES RED TWIST 2OZ 4136400222",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES BLACK LICORICE LICORICE TWIST 8OZ
4136400231", _
Replacement:="RED VINES BLACK LICORICE TWIST 8OZ 4136400231",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 8OZ
4136400232", _
Replacement:="RED VINES RED TWIST 8OZ 4136400232",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES ASSORTED LICORICE LICORICE BITE 8OZ
4136400240", _
Replacement:="RED VINES MIXED BITES 8OZ 4136400240",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES GRAPE LICORICE TWIST 8OZ 4136400246", _
Replacement:="RED VINES GRAPE TWIST 8OZ 4136400246",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES BLUE RASPBERRY LICORICE TWIST 8OZ
4136400247", _
Replacement:="RED VINES BLUE RASPBERRY TWIST 8OZ 4136400247",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES WATERMELON LICORICE TWIST 8OZ
4136400248", _
Replacement:="RED VINES WATERMELON TWIST 8OZ 4136400248",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES STRAWBERRY LICORICE TWIST 16OZ
4136400251", _
Replacement:="RED VINES JUMBO STRAWBERRY TWIST 16OZ
4136400251", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES CHERRY LICORICE TWIST 16OZ 4136400255",
_
Replacement:="RED VINES JUMBO CHERRY TWIST 16OZ 4136400255",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES STRAWBERRY LICORICE TWIST 5.5OZ
4136400271", _
Replacement:="RED VINES TRAY STRAWBERRY TWIST 5.5OZ
4136400271", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 6.5OZ
4136400273", _
Replacement:="RED VINES RED TWIST 6.5OZ 4136400273",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES CHERRY LICORICE TWIST 5.5OZ 4136400275",
_
Replacement:="RED VINES TRAY CHERRY TWIST 5.5OZ 4136400275",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

--
ALC
------------------------------------------------------------------------
ALC's Profile: http://www.excelforum.com/member.php...o&userid=25360
View this thread: http://www.excelforum.com/showthread...hreadid=394032

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Macro Help - Bolding info

Thank you!

Worked great!


--
ALC
------------------------------------------------------------------------
ALC's Profile: http://www.excelforum.com/member.php...o&userid=25360
View this thread: http://www.excelforum.com/showthread...hreadid=394032

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
Macro to put info into certain columns based on info in another co Studebaker Excel Discussion (Misc queries) 1 December 4th 07 05:27 PM
Bolding Row from VB6 Ian B[_2_] Excel Programming 4 July 9th 05 11:19 PM
Bolding when subtotalling mrsteve1 Excel Worksheet Functions 2 February 17th 05 11:26 PM
Bolding Dale[_12_] Excel Programming 2 September 30th 04 04:21 PM
Bolding specific text Scot Rundell Excel Programming 2 September 15th 03 07:20 AM


All times are GMT +1. The time now is 09:56 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement