| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and
extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b" at the start of it and "</b" at the end of it. How do I set this up in VB6? Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find and replace with bold in cells
The following function will return a string including <b and </b
tags from the text of cell R. Function BoldMarkup(R As Range) As String Dim N As Long Dim S As String Dim InBold As Boolean If R.Cells.Count 1 Then Exit Function End If If R.HasFormula = True Then Exit Function End If If Len(R.Text) = 0 Then Exit Function End If If Len(R.Text) = 1 Then If R.Characters(1, 1).Font.Bold Then BoldMarkup = "<b" & R.Text & "</b" Exit Function End If End If For N = 1 To Len(R.Text) If R.Characters(N, 1).Font.Bold = True Then If InBold = False Then S = S & "<b" & R.Characters(N, 1).Text InBold = True Else S = S & R.Characters(N, 1).Text If N = Len(R.Text) Then S = S & "</b" End If End If Else If InBold = True Then S = S & "</b" & R.Characters(N, 1).Text InBold = False Else S = S & R.Characters(N, 1).Text End If End If Next N BoldMarkup = S End Function Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Wed, 27 Jan 2010 14:09:02 -0800, Dan wrote: I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b" at the start of it and "</b" at the end of it. How do I set this up in VB6? Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find and replace with bold in cells
Chip,
Thank you for your reply. I have included your code in my program and it works some of the time. Here is a code snippet that I'm using: Dim CellRange As Excel.Range For I = 1 To 200 For J = 1 To 11 Set CellRange = ExcelWorksheet.Cells(I, J) TextStr = BoldMarkup(CellRange) Next J Next I If I set the upper limit of the "For J" loop to 1 (instead of 11), the code works. However, when I have it loop on the first 11 columns in the worksheet, it gives me the following error: Unable to set the Text Property of the Characters class The error occurs the first time that "R.Characters(N, 1).Text" is referenced in your function. I've tried to figure out what's wrong with my code that it's not interacting with your function properly, but I can't find anything that makes it work. Can you see how I should change my code to eliminate this error? Thanks! Dan "Chip Pearson" wrote: The following function will return a string including <b and </b tags from the text of cell R. Function BoldMarkup(R As Range) As String Dim N As Long Dim S As String Dim InBold As Boolean If R.Cells.Count 1 Then Exit Function End If If R.HasFormula = True Then Exit Function End If If Len(R.Text) = 0 Then Exit Function End If If Len(R.Text) = 1 Then If R.Characters(1, 1).Font.Bold Then BoldMarkup = "<b" & R.Text & "</b" Exit Function End If End If For N = 1 To Len(R.Text) If R.Characters(N, 1).Font.Bold = True Then If InBold = False Then S = S & "<b" & R.Characters(N, 1).Text InBold = True Else S = S & R.Characters(N, 1).Text If N = Len(R.Text) Then S = S & "</b" End If End If Else If InBold = True Then S = S & "</b" & R.Characters(N, 1).Text InBold = False Else S = S & R.Characters(N, 1).Text End If End If Next N BoldMarkup = S End Function Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Wed, 27 Jan 2010 14:09:02 -0800, Dan wrote: I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b" at the start of it and "</b" at the end of it. How do I set this up in VB6? Thanks! . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find and replace with bold in cells
Can anyone help me with this error message that I'm getting? Thanks!
"Dan" wrote: Chip, Thank you for your reply. I have included your code in my program and it works some of the time. Here is a code snippet that I'm using: Dim CellRange As Excel.Range For I = 1 To 200 For J = 1 To 11 Set CellRange = ExcelWorksheet.Cells(I, J) TextStr = BoldMarkup(CellRange) Next J Next I If I set the upper limit of the "For J" loop to 1 (instead of 11), the code works. However, when I have it loop on the first 11 columns in the worksheet, it gives me the following error: Unable to set the Text Property of the Characters class The error occurs the first time that "R.Characters(N, 1).Text" is referenced in your function. I've tried to figure out what's wrong with my code that it's not interacting with your function properly, but I can't find anything that makes it work. Can you see how I should change my code to eliminate this error? Thanks! Dan "Chip Pearson" wrote: The following function will return a string including <b and </b tags from the text of cell R. Function BoldMarkup(R As Range) As String Dim N As Long Dim S As String Dim InBold As Boolean If R.Cells.Count 1 Then Exit Function End If If R.HasFormula = True Then Exit Function End If If Len(R.Text) = 0 Then Exit Function End If If Len(R.Text) = 1 Then If R.Characters(1, 1).Font.Bold Then BoldMarkup = "<b" & R.Text & "</b" Exit Function End If End If For N = 1 To Len(R.Text) If R.Characters(N, 1).Font.Bold = True Then If InBold = False Then S = S & "<b" & R.Characters(N, 1).Text InBold = True Else S = S & R.Characters(N, 1).Text If N = Len(R.Text) Then S = S & "</b" End If End If Else If InBold = True Then S = S & "</b" & R.Characters(N, 1).Text InBold = False Else S = S & R.Characters(N, 1).Text End If End If Next N BoldMarkup = S End Function Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Wed, 27 Jan 2010 14:09:02 -0800, Dan wrote: I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b" at the start of it and "</b" at the end of it. How do I set this up in VB6? Thanks! . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find/Replace within links in cells | Excel Discussion (Misc queries) | |||
| Find and Replace only the end of a cells text | Excel Discussion (Misc queries) | |||
| Find and Replace 2 cells | Excel Discussion (Misc queries) | |||
| How to find multiple cells/replace whole cells w/data | Excel Discussion (Misc queries) | |||
| ??Find and Replace Duplicate Cells | Excel Programming | |||
Hybrid Mode
