Click here to Skip to main content
15,908,172 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello Sir
would you please help me to write code to add underline to one or more of word inside a cell ..for example
in cell g1, h1, i1, j1, k1
i want to get the value of all that cells and put it in cell a1
and i want to add underline to the cel h1 and the cell j1
h1 and j1 are a number has format like ...1,000,000

value of cells g1, i1, k1 are words

so how can i get all this cells value and put all in one cell a1 with underline for only h1 and j1
i wish some one can help me
thanks for all

What I have tried:

i'm trying to get value of cells (for example G1, H1, I1 and K1) and put it in one cell with underline to cell H1 and J1

like as showing bellow :

G1     H1           i1          J1        K1
word1 1,000,000    word2     2,000,000   word3


i want to use Excel Formula or Excel VBA to get the result as showing below
word1 1,000,000 word2 2,000,000 word3


withunderline for H1 and J1
Posted
Updated 20-Jun-18 1:29am
v2

Check this:
VB
Option Explicit 'force variable declaration

Sub JoinTextAndUnderline()
    'variables
    Dim wsh As Worksheet
    Dim i As Integer, beg As Integer, lng As Integer
    Dim sTmp As String
    'initialize worksheet object
    Set wsh = ThisWorkbook.Worksheets("Sheet1")
    'set text to cell A1
    wsh.Range("A1") = wsh.Range("G1") & " " & wsh.Range("H1") & " " & _
        wsh.Range("I1") & " " & wsh.Range("J1") & " " & wsh.Range("K1")
    'change formatting
    'loop through the cells in column-range: (G:J)
    For i = 0 To 4
        sTmp = sTmp & wsh.Range("G1").Offset(ColumnOffset:=i) & " "
        Select Case i
            Case 1, 3
                'get the beginning of text to underline
                beg = Len(sTmp) - Len(wsh.Range("G1").Offset(ColumnOffset:=i).Text)
                'get the length of text to underline
                lng = Len(wsh.Range("G1").Offset(ColumnOffset:=i).Text)
                'underline
                wsh.Range("A1").Characters(beg, lng).Font.Underline = True
        End Select
    Next
    'clean up!
    Set wsh = Nothing
End Sub


Good luck!
 
Share this answer
 
v2
Comments
Richard MacCutchan 20-Jun-18 12:28pm    
Cool.
Maciej Los 21-Jun-18 1:51am    
Thank you, Richard.
Something like:
VB
Range("H1").Select
Selection.Font.Underline = xlUnderlineStyleSingle

You can always find sample code by using the Macro record feature of Excel.
 
Share this answer
 
Comments
Maciej Los 20-Jun-18 5:40am    
I'm able to accept all, but not [Select]!
A proper way to do it is:
SheetName.Range("H1").Font.Underline = xlUnderlineStyleSingle
Richard MacCutchan 20-Jun-18 6:17am    
Fine, but my solution still works.
Maciej Los 20-Jun-18 6:28am    
I didn't say it doesn't work ;)
I was trying to say that using [Select] method is very bad VBA programming practice, because it fires several events in MS Excel. It may affects on CPU and RAM usage, etc - an efficiency in overall.
Richard MacCutchan 20-Jun-18 6:33am    
Well, I am not an expert in Excel programming. I just use Macro Record, and if it works that's good enough.
Maciej Los 20-Jun-18 6:36am    
:laugh:
Yes, this is quite good enough on start. But later may cause several issues...
Cheers,
Maciej
thank you but it's not working
 
Share this answer
 
Comments
Maciej Los 20-Jun-18 5:40am    
This is not an answer. Please, delete it to avoid down-voting. To post comment, use "Have a question or comment" widget.
Richard MacCutchan 20-Jun-18 6:16am    
That's because you are doing something wrong.
Maciej Los 20-Jun-18 7:30am    
Respect the rules! Delete this!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900