Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
On Sat, 28 Oct 2006 05:39:01 -0700, Steen
wrote: You cannot in Excel. You can bold portions of strings, but not strings generated by formulas. with a string in a cell, select the portion you wish bolded, and then select Bold from the formatting controls. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
Hi again
Any chance that you could help me with converting is to a string and make the headline part bold? "Ron Rosenfeld" wrote: On Sat, 28 Oct 2006 05:39:01 -0700, Steen wrote: You cannot in Excel. You can bold portions of strings, but not strings generated by formulas. with a string in a cell, select the portion you wish bolded, and then select Bold from the formatting controls. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
Copy and Paste Special with turn =A1&A2 in B1 to a string
(Copy B1 and then Paste Special in B1) Now you can format part of the string. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Steen" wrote in message ... Hi again Any chance that you could help me with converting is to a string and make the headline part bold? "Ron Rosenfeld" wrote: On Sat, 28 Oct 2006 05:39:01 -0700, Steen wrote: You cannot in Excel. You can bold portions of strings, but not strings generated by formulas. with a string in a cell, select the portion you wish bolded, and then select Bold from the formatting controls. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
On Sat, 28 Oct 2006 08:59:01 -0700, Steen
wrote: Hi again Any chance that you could help me with converting is to a string and make the headline part bold? "Ron Rosenfeld" wrote: On Sat, 28 Oct 2006 05:39:01 -0700, Steen wrote: You cannot in Excel. You can bold portions of strings, but not strings generated by formulas. with a string in a cell, select the portion you wish bolded, and then select Bold from the formatting controls. --ron Select the cell in which you have the string concatenation. Place your cursor in the formula bar. <F9 <Enter In formula bar, select "headline part" Then, either Press the "B" button on the format toolbar or Format/Cells/Font Style BOLD --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
Thanks to all, but it dosn't solve my problem, because I want it to be done
automatically - any help on that? A B C HeadLine1 Decription1 HeadLine+Desc1 C1= (BOLD(A1)&char(10)&B1 HeadLine2 Decription2 HeadLine+Desc2 C2= (BOLD(A2)&char(10)&B2 HeadLine3 Decription3 HeadLine+Desc3 C3= (BOLD(A3)&char(10)&B3 .... Any help here? "Ron Rosenfeld" wrote: On Sat, 28 Oct 2006 08:59:01 -0700, Steen wrote: Hi again Any chance that you could help me with converting is to a string and make the headline part bold? "Ron Rosenfeld" wrote: On Sat, 28 Oct 2006 05:39:01 -0700, Steen wrote: You cannot in Excel. You can bold portions of strings, but not strings generated by formulas. with a string in a cell, select the portion you wish bolded, and then select Bold from the formatting controls. --ron Select the cell in which you have the string concatenation. Place your cursor in the formula bar. <F9 <Enter In formula bar, select "headline part" Then, either Press the "B" button on the format toolbar or Format/Cells/Font Style BOLD --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
On Sun, 29 Oct 2006 02:03:02 -0800, Steen
wrote: Thanks to all, but it dosn't solve my problem, because I want it to be done automatically - any help on that? A B C HeadLine1 Decription1 HeadLine+Desc1 C1= (BOLD(A1)&char(10)&B1 HeadLine2 Decription2 HeadLine+Desc2 C2= (BOLD(A2)&char(10)&B2 HeadLine3 Decription3 HeadLine+Desc3 C3= (BOLD(A3)&char(10)&B3 ... Any help here? As already written, you cannot use worksheet functions to accomplish this. You'll have to use VBA. You can trigger a macro either manually or automatically. This code should get you started with one approach, but requires modifications for appropriate error checking and column width formatting. You could also make the code run automatically. To enter the code, <alt<F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this, position the cursor in some cell in Column C. <alt<F8 opens the Macro Dialog Box. Select the macro and RUN. ====================================== Option Explicit Sub BoldHeadline() 'Concatenates the two cells to the left of Selection 'Bolds contents of first cell 'Inserts CHAR(10) Dim HLcount As Long Dim HL As Range, Descr As Range Dim Target As Range Set Target = Selection For Each Target In Selection Set HL = Target.Offset(0, -2) Set Descr = Target.Offset(0, -1) HLcount = Len(HL.Text) Target.Value = HL.Text & vbLf & Descr.Text Target.Characters(1, HLcount).Font.Bold = True Next Target End Sub =============================== --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
Hi Ron
Thank again - that works fine. Can you help me implement this in the code below?: ------------------------------------------------------ Private Sub Update_Click() Dim session Dim temp Dim queryDefObj Dim resSet Dim status Dim count Dim records Dim reply Dim filter Dim strId 'write status Range("A1").Value = "Opening session..." 'make link to clearquest database and log in Set session = CreateObject("CLEARQUEST.SESSION") session.UserLogon "roka", "", "VMP", AD_PRIVATE_SESSION, "" 'write status Range("A1").Value = "Session opened" iRowNo = 8 While Range("A" & iRowNo).Value Like "SCO" Or Range("A" & iRowNo).Value Like "SPR" 'generate VMP000XXX id with precisely 11 chars strId = "VMP" While Len(Range("B" & iRowNo).Value) + Len(strId) < 11 strId = strId & "0" Wend strId = strId & Range("B" & iRowNo).Value 'define SQL query sqlString = "select T1.headline, T1.description from " & Range("A" & iRowNo).Value & " T1 where id='" & strId & "'" Set resSet = session.BuildSQLQuery(sqlString) 'execute query resSet.EnableRecordCount resSet.Execute Range("A1").Value = "Executing query..." If resSet.RecordCount() = 0 Then MsgBox ("Query did not return anything in line " & iRowNo) End If 'run through data resSet.MoveNext For k = 1 To resSet.RecordCount() Range("C" & iRowNo).Value = resSet.GetColumnValue(1) & vbLf & resSet.GetColumnValue(2) resSet.MoveNext Next iRowNo = iRowNo + 1 Wend Range("A1").Value = "Query finished" End Sub --------------------------------------------------------- "Ron Rosenfeld" wrote: On Sun, 29 Oct 2006 02:03:02 -0800, Steen wrote: Thanks to all, but it dosn't solve my problem, because I want it to be done automatically - any help on that? A B C HeadLine1 Decription1 HeadLine+Desc1 C1= (BOLD(A1)&char(10)&B1 HeadLine2 Decription2 HeadLine+Desc2 C2= (BOLD(A2)&char(10)&B2 HeadLine3 Decription3 HeadLine+Desc3 C3= (BOLD(A3)&char(10)&B3 ... Any help here? As already written, you cannot use worksheet functions to accomplish this. You'll have to use VBA. You can trigger a macro either manually or automatically. This code should get you started with one approach, but requires modifications for appropriate error checking and column width formatting. You could also make the code run automatically. To enter the code, <alt<F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this, position the cursor in some cell in Column C. <alt<F8 opens the Macro Dialog Box. Select the macro and RUN. ====================================== Option Explicit Sub BoldHeadline() 'Concatenates the two cells to the left of Selection 'Bolds contents of first cell 'Inserts CHAR(10) Dim HLcount As Long Dim HL As Range, Descr As Range Dim Target As Range Set Target = Selection For Each Target In Selection Set HL = Target.Offset(0, -2) Set Descr = Target.Offset(0, -1) HLcount = Len(HL.Text) Target.Value = HL.Text & vbLf & Descr.Text Target.Characters(1, HLcount).Font.Bold = True Next Target End Sub =============================== --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
On Sun, 29 Oct 2006 07:21:02 -0800, Steen
wrote: Hi Ron Thank again - that works fine. Can you help me implement this in the code below?: I'm not really familiar with SQL queries, but see my suggestion below, inline. ------------------------------------------------------ Private Sub Update_Click() Dim session Dim temp Dim queryDefObj Dim resSet Dim status Dim count Dim records Dim reply Dim filter Dim strId 'write status Range("A1").Value = "Opening session..." 'make link to clearquest database and log in Set session = CreateObject("CLEARQUEST.SESSION") session.UserLogon "roka", "", "VMP", AD_PRIVATE_SESSION, "" 'write status Range("A1").Value = "Session opened" iRowNo = 8 While Range("A" & iRowNo).Value Like "SCO" Or Range("A" & iRowNo).Value Like "SPR" 'generate VMP000XXX id with precisely 11 chars strId = "VMP" While Len(Range("B" & iRowNo).Value) + Len(strId) < 11 strId = strId & "0" Wend strId = strId & Range("B" & iRowNo).Value 'define SQL query sqlString = "select T1.headline, T1.description from " & Range("A" & iRowNo).Value & " T1 where id='" & strId & "'" Set resSet = session.BuildSQLQuery(sqlString) 'execute query resSet.EnableRecordCount resSet.Execute Range("A1").Value = "Executing query..." If resSet.RecordCount() = 0 Then MsgBox ("Query did not return anything in line " & iRowNo) End If 'run through data resSet.MoveNext For k = 1 To resSet.RecordCount() Range("C" & iRowNo).Value = resSet.GetColumnValue(1) & vbLf & resSet.GetColumnValue(2) -- Insert BOLD routine here If the above code results in a text string, then something like: Range("C" & iRowNo).Characters(1, Len(resSet.GetColumnValue(1)) _ .Font.Bold = True might work. resSet.MoveNext Next iRowNo = iRowNo + 1 Wend Range("A1").Value = "Query finished" End Sub --------------------------------------------------------- "Ron Rosenfeld" wrote: On Sun, 29 Oct 2006 02:03:02 -0800, Steen wrote: Thanks to all, but it dosn't solve my problem, because I want it to be done automatically - any help on that? A B C HeadLine1 Decription1 HeadLine+Desc1 C1= (BOLD(A1)&char(10)&B1 HeadLine2 Decription2 HeadLine+Desc2 C2= (BOLD(A2)&char(10)&B2 HeadLine3 Decription3 HeadLine+Desc3 C3= (BOLD(A3)&char(10)&B3 ... Any help here? As already written, you cannot use worksheet functions to accomplish this. You'll have to use VBA. You can trigger a macro either manually or automatically. This code should get you started with one approach, but requires modifications for appropriate error checking and column width formatting. You could also make the code run automatically. To enter the code, <alt<F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this, position the cursor in some cell in Column C. <alt<F8 opens the Macro Dialog Box. Select the macro and RUN. ====================================== Option Explicit Sub BoldHeadline() 'Concatenates the two cells to the left of Selection 'Bolds contents of first cell 'Inserts CHAR(10) Dim HLcount As Long Dim HL As Range, Descr As Range Dim Target As Range Set Target = Selection For Each Target In Selection Set HL = Target.Offset(0, -2) Set Descr = Target.Offset(0, -1) HLcount = Len(HL.Text) Target.Value = HL.Text & vbLf & Descr.Text Target.Characters(1, HLcount).Font.Bold = True Next Target End Sub =============================== --ron --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
Hi Again
Thats worked realy fine - help much appriciated. "Ron Rosenfeld" wrote: On Sun, 29 Oct 2006 07:21:02 -0800, Steen wrote: Hi Ron Thank again - that works fine. Can you help me implement this in the code below?: I'm not really familiar with SQL queries, but see my suggestion below, inline. ------------------------------------------------------ Private Sub Update_Click() Dim session Dim temp Dim queryDefObj Dim resSet Dim status Dim count Dim records Dim reply Dim filter Dim strId 'write status Range("A1").Value = "Opening session..." 'make link to clearquest database and log in Set session = CreateObject("CLEARQUEST.SESSION") session.UserLogon "roka", "", "VMP", AD_PRIVATE_SESSION, "" 'write status Range("A1").Value = "Session opened" iRowNo = 8 While Range("A" & iRowNo).Value Like "SCO" Or Range("A" & iRowNo).Value Like "SPR" 'generate VMP000XXX id with precisely 11 chars strId = "VMP" While Len(Range("B" & iRowNo).Value) + Len(strId) < 11 strId = strId & "0" Wend strId = strId & Range("B" & iRowNo).Value 'define SQL query sqlString = "select T1.headline, T1.description from " & Range("A" & iRowNo).Value & " T1 where id='" & strId & "'" Set resSet = session.BuildSQLQuery(sqlString) 'execute query resSet.EnableRecordCount resSet.Execute Range("A1").Value = "Executing query..." If resSet.RecordCount() = 0 Then MsgBox ("Query did not return anything in line " & iRowNo) End If 'run through data resSet.MoveNext For k = 1 To resSet.RecordCount() Range("C" & iRowNo).Value = resSet.GetColumnValue(1) & vbLf & resSet.GetColumnValue(2) -- Insert BOLD routine here If the above code results in a text string, then something like: Range("C" & iRowNo).Characters(1, Len(resSet.GetColumnValue(1)) _ .Font.Bold = True might work. resSet.MoveNext Next iRowNo = iRowNo + 1 Wend Range("A1").Value = "Query finished" End Sub --------------------------------------------------------- "Ron Rosenfeld" wrote: On Sun, 29 Oct 2006 02:03:02 -0800, Steen wrote: Thanks to all, but it dosn't solve my problem, because I want it to be done automatically - any help on that? A B C HeadLine1 Decription1 HeadLine+Desc1 C1= (BOLD(A1)&char(10)&B1 HeadLine2 Decription2 HeadLine+Desc2 C2= (BOLD(A2)&char(10)&B2 HeadLine3 Decription3 HeadLine+Desc3 C3= (BOLD(A3)&char(10)&B3 ... Any help here? As already written, you cannot use worksheet functions to accomplish this. You'll have to use VBA. You can trigger a macro either manually or automatically. This code should get you started with one approach, but requires modifications for appropriate error checking and column width formatting. You could also make the code run automatically. To enter the code, <alt<F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this, position the cursor in some cell in Column C. <alt<F8 opens the Macro Dialog Box. Select the macro and RUN. ====================================== Option Explicit Sub BoldHeadline() 'Concatenates the two cells to the left of Selection 'Bolds contents of first cell 'Inserts CHAR(10) Dim HLcount As Long Dim HL As Range, Descr As Range Dim Target As Range Set Target = Selection For Each Target In Selection Set HL = Target.Offset(0, -2) Set Descr = Target.Offset(0, -1) HLcount = Len(HL.Text) Target.Value = HL.Text & vbLf & Descr.Text Target.Characters(1, HLcount).Font.Bold = True Next Target End Sub =============================== --ron --ron |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
On Sun, 29 Oct 2006 12:21:01 -0800, Steen
wrote: Hi Again Thats worked realy fine - help much appriciated. You're welcome. I'm glad it worked and thanks for the feedback. --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
I searched on CreateObject("CLEARQUEST.SESSION") and your post turned
up. I'm trying to figure out how to be able to connect to a ClearQuest session, - then to a particular ticket number, and then to update a few of the fields. Where would I find the info for how to connect to ClearQuest? I tried searching IBM's site. There must be a CQ user's group somewhere? Thanks for your help, - Cole Ron Rosenfeld wrote: On Sun, 29 Oct 2006 12:21:01 -0800, Steen wrote: Hi Again Thats worked realy fine - help much appriciated. You're welcome. I'm glad it worked and thanks for the feedback. --ron |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bold in string concatenation
On 6 Nov 2006 14:23:20 -0800, "Cole" wrote:
I searched on CreateObject("CLEARQUEST.SESSION") and your post turned up. I'm trying to figure out how to be able to connect to a ClearQuest session, - then to a particular ticket number, and then to update a few of the fields. Where would I find the info for how to connect to ClearQuest? I tried searching IBM's site. There must be a CQ user's group somewhere? Thanks for your help, - Cole Hopefully Steen will see this post. My contribution was merely limited to bolding a portion of the string. I'm not knowledgeable about the area you are asking. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help setting the worksheet header/Footer margins based on string height? | Excel Discussion (Misc queries) | |||
List File Properties - Author | Excel Worksheet Functions | |||
macro to bold number of chars from end of a string | Excel Discussion (Misc queries) | |||
Bold a portion of concatenated string | Excel Discussion (Misc queries) | |||
How do I put each member of string concatenation in its own line? | Excel Worksheet Functions |