Home |
Search |
Today's Posts |
#1
|
|||
|
|||
macro to bold number of chars from end of a string
Hi all, (first time I've used a forum such as this)
I have a work book that produces a two page document by using concatenate and information entered on the first worksheet. What I need to do is make a number of chars in a string bold. I can't use; With ActiveCell.Characters(Start:=118, Length:=8).Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 As there is a varying length to the string due to the company name being cancatenated in. Tried changing start to end but this did not work. The characters I need bold are dates and will also be the only numbers in the string. I've also tried recording a macro to create the formula =RIGHT(B46,60) to get the start of the text I need bold, paste it into a new cell and format the needed chars to bold. Then all I need to do in insert the test before in in that cell so to keep the formatting. This works but the recoded macro uses the string of text when I need it to use the cell reference for the company name. =CONCATENATE("In the event of ",'Control Sheet'!E5,"'s"," failure to remedy the Payment Breach We will refuse to accept any new orders for the Service and/or suspend access to the Gateway on ",TEXT(Calender!AB5,"dd/mm/yyyy")," and/or exercise its right to terminate the Contract and the Service provided under it at 12:00 on ",TEXT(Calender!AD5,"dd/mm/yyyy"),".") So to surmise I need to paste the above to a new worksheet as values and make the dates bold, but because the value in 'Control Sheet'!E5 is going to be of a varying length I'm having trouble. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to bold number of chars from end of a string
if cell A1 contains the text string "bob 18/1/06 fred 24/1/06 dick" you can search for a number between 0 and 9 , this will return the position of the various numbers within the string. Use the lowest number. LEN(A1) will give you the string length, can you work with this? -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=547812 |
#3
|
|||
|
|||
Quote:
ActiveCell = Application.SEARCH(0,ActiveCell,1) ActiveCell = Application.SEARCH(1,ActiveCell,1) ActiveCell = Application.SEARCH(2,ActiveCell,1) ActiveCell = Application.SEARCH(3,ActiveCell,1) ....etc But then how do I use the lowest number and use this to be the value of Start in the following? With ActiveCell.Characters(Start:=118, Length:=8).Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 I also have the problem that each of my text strings has two dates through the data. So the above method would be fine to make the first date bold but I will have trouble making the second date bold. I suppose there must be a way as each date uses 8 characters. So the second date will have a start point of the ninth lowest value returned from using Application.Search. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to bold number of chars from end of a string
In your original post, you used a format of dd/mm/yyyy. In this example, you
lost the leading 0's in the day and only used two digit years. This will work if you really use a format of dd/mm/yyyy. (But remember, you have to be formatting values--not formulas--formulas can't use this kind of character by character formatting.) Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim iCtr As Long Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, _ Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No constants" Exit Sub End If For Each myCell In myRng.Cells For iCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, iCtr, 10) Like "##/##/####" Then With myCell.Characters(Start:=iCtr, Length:=10).Font .Name = "Arial" .Bold = True .Size = 12 End With End If Next iCtr Next myCell End Sub herbwarri0r wrote: robert111 Wrote: if cell A1 contains the text string "bob 18/1/06 fred 24/1/06 dick" you can search for a number between 0 and 9 , this will return the position of the various numbers within the string. Use the lowest number. LEN(A1) will give you the string length, can you work with this? -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=547812 Still a bit unsure of how I can do this in a macro. I can use; ActiveCell = Application.SEARCH(0,ActiveCell,1) ActiveCell = Application.SEARCH(1,ActiveCell,1) ActiveCell = Application.SEARCH(2,ActiveCell,1) ActiveCell = Application.SEARCH(3,ActiveCell,1) ....etc But then how do I use the lowest number and use this to be the value of Start in the following? With ActiveCell.Characters(Start:=118, Length:=8).Font Name = "Arial" FontStyle = "Bold" Size = 12 I also have the problem that each of my text strings has two dates through the data. So the above method would be fine to make the first date bold but I will have trouble making the second date bold. I suppose there must be a way as each date uses 8 characters. So the second date will have a start point of the ninth lowest value returned from using Application.Search. -- herbwarri0r -- Dave Peterson |
#5
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting a number from a string and converting it into an Intege | Excel Worksheet Functions | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
converting number string to number with decimal | Excel Worksheet Functions | |||
last number array from string | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |