Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 28th 06, 01:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 104
Default Bold in string concatenation



  #2   Report Post  
Old October 28th 06, 02:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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   Report Post  
Old October 28th 06, 04:59 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 104
Default 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   Report Post  
Old October 28th 06, 05:24 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default 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   Report Post  
Old October 28th 06, 10:41 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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   Report Post  
Old October 29th 06, 10:03 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 104
Default 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   Report Post  
Old October 29th 06, 11:51 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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   Report Post  
Old October 29th 06, 03:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 104
Default 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   Report Post  
Old October 29th 06, 03:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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   Report Post  
Old October 29th 06, 08:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 104
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Need help setting the worksheet header/Footer margins based on string height? Doug Excel Discussion (Misc queries) 0 August 20th 06 02:05 AM
List File Properties - Author SS Excel Worksheet Functions 1 June 23rd 06 04:56 PM
macro to bold number of chars from end of a string herbwarri0r Excel Discussion (Misc queries) 4 June 6th 06 01:21 PM
Bold a portion of concatenated string joeeng Excel Discussion (Misc queries) 1 December 8th 05 08:26 PM
How do I put each member of string concatenation in its own line? DViolette Excel Worksheet Functions 3 July 8th 05 02:08 AM


All times are GMT +1. The time now is 11:08 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017