With Excel 2007

I have data (both text and numbers) in column A. Some of the "text" is
underlined. I would like to place an €œx€� in column B next to the data in
column A that is underlined. See below. I would like to place the €œx€� in
column B in the easiest manner possible, automatically without placing the
"x" manually for each item since I have a few thousand rows. Also, I would
like to do this without using a macro.

Since I am unable to underline in this post, the following data in column A
is underlined. Also, you can see an "x" in column B corresponding to this
data:

Fruits
Meat
Milk
Fish
Lamb


Any suggestions.

Thank you,

Bill



Column A Column B

Fruits x
Vegetables
5
Meat x
6
Milk x
Bread
Beverage
Fish x
9
Lamb x
Soup

as long as it's a single underline, this may work for you

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastrow
With ws.Range("A" & i)
If .Font.Underline = 2 Then
.Offset(, 1).Value = "X"
End If
End With
Next
End Sub


--


Gary Keramidas
Excel 2003


"Bill" wrote in message
...
With Excel 2007

I have data (both text and numbers) in column A. Some of the "text" is
underlined. I would like to place an €œx€� in column B next to the data in
column A that is underlined. See below. I would like to place the €œx€� in
column B in the easiest manner possible, automatically without placing the
"x" manually for each item since I have a few thousand rows. Also, I would
like to do this without using a macro.

Since I am unable to underline in this post, the following data in column
A
is underlined. Also, you can see an "x" in column B corresponding to this
data:

Fruits
Meat
Milk
Fish
Lamb


Any suggestions.

Thank you,

Bill



Column A Column B

Fruits x
Vegetables
5
Meat x
6
Milk x
Bread
Beverage
Fish x
9
Lamb x
Soup

Gary:

I need a little more help on your suggestion. I do not know what Dim, Sub
etc. are. Could you please give me more of a step by step procedure to
follow.

Thank you,

Bill


"Gary Keramidas" wrote:

as long as it's a single underline, this may work for you

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastrow
With ws.Range("A" & i)
If .Font.Underline = 2 Then
.Offset(, 1).Value = "X"
End If
End With
Next
End Sub


--


Gary Keramidas
Excel 2003


"Bill" wrote in message
...
With Excel 2007

I have data (both text and numbers) in column A. Some of the "text" is
underlined. I would like to place an €œx€� in column B next to the data in
column A that is underlined. See below. I would like to place the €œx€� in
column B in the easiest manner possible, automatically without placing the
"x" manually for each item since I have a few thousand rows. Also, I would
like to do this without using a macro.

Since I am unable to underline in this post, the following data in column
A
is underlined. Also, you can see an "x" in column B corresponding to this
data:

Fruits
Meat
Milk
Fish
Lamb


Any suggestions.

Thank you,

Bill



Column A Column B

Fruits x
Vegetables
5
Meat x
6
Milk x
Bread
Beverage
Fish x
9
Lamb x
Soup





.

in excel. press alt-F11 to get to the code editor

click insert on the menu and choose module

paste the code into the module

change sheet1 in the following line to match your sheet name

Set ws = Worksheets("Sheet1")

then press f5

close the vb editor and se if you get the intended results.

if not, don't save the workbook.

--


Gary Keramidas
Excel 2003


"Bill" wrote in message
...
Gary:

I need a little more help on your suggestion. I do not know what Dim, Sub
etc. are. Could you please give me more of a step by step procedure to
follow.

Thank you,

Bill


"Gary Keramidas" wrote:

as long as it's a single underline, this may work for you

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastrow
With ws.Range("A" & i)
If .Font.Underline = 2 Then
.Offset(, 1).Value = "X"
End If
End With
Next
End Sub


--


Gary Keramidas
Excel 2003


"Bill" wrote in message
...
With Excel 2007

I have data (both text and numbers) in column A. Some of the "text" is
underlined. I would like to place an €œx€� in column B next to the data
in
column A that is underlined. See below. I would like to place the €œx€�
in
column B in the easiest manner possible, automatically without placing
the
"x" manually for each item since I have a few thousand rows. Also, I
would
like to do this without using a macro.

Since I am unable to underline in this post, the following data in
column
A
is underlined. Also, you can see an "x" in column B corresponding to
this
data:

Fruits
Meat
Milk
Fish
Lamb


Any suggestions.

Thank you,

Bill



Column A Column B

Fruits x
Vegetables
5
Meat x
6
Milk x
Bread
Beverage
Fish x
9
Lamb x
Soup





.

There are several styles of underline (single, double, etc) so the
following code tests whether any sort of underlining is applied to the
cell. In other words, whether the underline property is anything
except none. This first proc tests the entire cell. Change the line
marked with '<<< to the first row number of your data.


Sub UnderlineOfFullCell()
Dim LastRow As Long
Dim WS As Worksheet
Dim Ndx As Long
Dim StartRow As Long
Set WS = ActiveSheet
StartRow = 1 '<<<<
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Application.ScreenUpdating = False
For Ndx = StartRow To LastRow
If WS.Cells(Ndx, "A").Font.Underline < xlUnderlineStyleNone Then
WS.Cells(Ndx, "B").Value = "x"
Else
WS.Cells(Ndx, "B").Value = vbNullString
End If
Next Ndx
End Sub

It is also possible that only some of the characters in the cell have
underlining applied while other characters are not underlined. This
second proc tests whether any character in the cell has underlining.
This proc will return the same results as the proc above, so you need
not have both procedures. This second proc will detect underlining is
applied to the entire cell or to any content in the cell. As above,
change the line marked with '<<< to the starting row of your data.


Sub UnderlineOfPartOfCell()
Dim LastRow As Long
Dim WS As Worksheet
Dim Ndx As Long
Dim R As Range
Dim StartRow As Long
Dim N As Long
Set WS = ActiveSheet
StartRow = 1 '<<<<
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Application.ScreenUpdating = False
For Ndx = StartRow To LastRow
Set R = WS.Cells(1, "A")
R(1, 2).Value = vbNullString
For N = 1 To R.Characters.Count
If R.Characters(N, 1).Font.Underline < _
xlUnderlineStyleNone Then
R(1, 2).Value = "x"
Exit For
End If
Next N
Next Ndx
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sun, 7 Feb 2010 11:06:01 -0800, Bill
wrote:

With Excel 2007

I have data (both text and numbers) in column A. Some of the "text" is
underlined. I would like to place an “x” in column B next to the data in
column A that is underlined. See below. I would like to place the “x” in
column B in the easiest manner possible, automatically without placing the
"x" manually for each item since I have a few thousand rows. Also, I would
like to do this without using a macro.

Since I am unable to underline in this post, the following data in column A
is underlined. Also, you can see an "x" in column B corresponding to this
data:

Fruits
Meat
Milk
Fish
Lamb


Any suggestions.

Thank you,

Bill



Column A Column B

Fruits x
Vegetables
5
Meat x
6
Milk x
Bread
Beverage
Fish x
9
Lamb x
Soup

Chip:

I have very limited knowledge of proc, codes, etc. Could you please offer a
simple step by step method for me to follow, almost 101 material. Also, I
have just a single underline.

Thank you,

Bill

"Chip Pearson" wrote:

There are several styles of underline (single, double, etc) so the
following code tests whether any sort of underlining is applied to the
cell. In other words, whether the underline property is anything
except none. This first proc tests the entire cell. Change the line
marked with '<<< to the first row number of your data.


Sub UnderlineOfFullCell()
Dim LastRow As Long
Dim WS As Worksheet
Dim Ndx As Long
Dim StartRow As Long
Set WS = ActiveSheet
StartRow = 1 '<<<<
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Application.ScreenUpdating = False
For Ndx = StartRow To LastRow
If WS.Cells(Ndx, "A").Font.Underline < xlUnderlineStyleNone Then
WS.Cells(Ndx, "B").Value = "x"
Else
WS.Cells(Ndx, "B").Value = vbNullString
End If
Next Ndx
End Sub

It is also possible that only some of the characters in the cell have
underlining applied while other characters are not underlined. This
second proc tests whether any character in the cell has underlining.
This proc will return the same results as the proc above, so you need
not have both procedures. This second proc will detect underlining is
applied to the entire cell or to any content in the cell. As above,
change the line marked with '<<< to the starting row of your data.


Sub UnderlineOfPartOfCell()
Dim LastRow As Long
Dim WS As Worksheet
Dim Ndx As Long
Dim R As Range
Dim StartRow As Long
Dim N As Long
Set WS = ActiveSheet
StartRow = 1 '<<<<
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Application.ScreenUpdating = False
For Ndx = StartRow To LastRow
Set R = WS.Cells(1, "A")
R(1, 2).Value = vbNullString
For N = 1 To R.Characters.Count
If R.Characters(N, 1).Font.Underline < _
xlUnderlineStyleNone Then
R(1, 2).Value = "x"
Exit For
End If
Next N
Next Ndx
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sun, 7 Feb 2010 11:06:01 -0800, Bill
wrote:

With Excel 2007

I have data (both text and numbers) in column A. Some of the "text" is
underlined. I would like to place an €œx€� in column B next to the data in
column A that is underlined. See below. I would like to place the €œx€� in
column B in the easiest manner possible, automatically without placing the
"x" manually for each item since I have a few thousand rows. Also, I would
like to do this without using a macro.

Since I am unable to underline in this post, the following data in column A
is underlined. Also, you can see an "x" in column B corresponding to this
data:

Fruits
Meat
Milk
Fish
Lamb


Any suggestions.

Thank you,

Bill



Column A Column B

Fruits x
Vegetables
5
Meat x
6
Milk x
Bread
Beverage
Fish x
9
Lamb x
Soup




.

With your workbook open, press ALT F11 to open the VBA editor. On the
left side of the screen you should see the Project window, which is a
tree view control listing all open workbooks. If you do not see this
window, choose it from the View menu or just press CTRL R. Find your
workbook (it might be the only one listed), and click on it. Then, go
to the Insert menu and choose "Module" (not Class Module). That will
create a new code module named "Module1" and open it in the main
window of the VBA editor. Copy the code beginning at

Sub UnderlineOfPartOfCell()

and all the way down to

End Sub

Change the StartRow value (marked with '<<<") to the row on which your
data begins. Then, close the VBA editor from the File menu. Back in
Excel, press ALT F8 to display the Macros dialog, click on
UnderlineOfPartOfCell and then click Run. This will run the code.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sun, 7 Feb 2010 12:41:01 -0800, Bill
wrote:

Chip:

I have very limited knowledge of proc, codes, etc. Could you please offer a
simple step by step method for me to follow, almost 101 material. Also, I
have just a single underline.

Thank you,

Bill

"Chip Pearson" wrote:

There are several styles of underline (single, double, etc) so the
following code tests whether any sort of underlining is applied to the
cell. In other words, whether the underline property is anything
except none. This first proc tests the entire cell. Change the line
marked with '<<< to the first row number of your data.


Sub UnderlineOfFullCell()
Dim LastRow As Long
Dim WS As Worksheet
Dim Ndx As Long
Dim StartRow As Long
Set WS = ActiveSheet
StartRow = 1 '<<<<
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Application.ScreenUpdating = False
For Ndx = StartRow To LastRow
If WS.Cells(Ndx, "A").Font.Underline < xlUnderlineStyleNone Then
WS.Cells(Ndx, "B").Value = "x"
Else
WS.Cells(Ndx, "B").Value = vbNullString
End If
Next Ndx
End Sub

It is also possible that only some of the characters in the cell have
underlining applied while other characters are not underlined. This
second proc tests whether any character in the cell has underlining.
This proc will return the same results as the proc above, so you need
not have both procedures. This second proc will detect underlining is
applied to the entire cell or to any content in the cell. As above,
change the line marked with '<<< to the starting row of your data.


Sub UnderlineOfPartOfCell()
Dim LastRow As Long
Dim WS As Worksheet
Dim Ndx As Long
Dim R As Range
Dim StartRow As Long
Dim N As Long
Set WS = ActiveSheet
StartRow = 1 '<<<<
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Application.ScreenUpdating = False
For Ndx = StartRow To LastRow
Set R = WS.Cells(1, "A")
R(1, 2).Value = vbNullString
For N = 1 To R.Characters.Count
If R.Characters(N, 1).Font.Underline < _
xlUnderlineStyleNone Then
R(1, 2).Value = "x"
Exit For
End If
Next N
Next Ndx
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sun, 7 Feb 2010 11:06:01 -0800, Bill
wrote:

With Excel 2007

I have data (both text and numbers) in column A. Some of the "text" is
underlined. I would like to place an “x” in column B next to the data in
column A that is underlined. See below. I would like to place the “x” in
column B in the easiest manner possible, automatically without placing the
"x" manually for each item since I have a few thousand rows. Also, I would
like to do this without using a macro.

Since I am unable to underline in this post, the following data in column A
is underlined. Also, you can see an "x" in column B corresponding to this
data:

Fruits
Meat
Milk
Fish
Lamb


Any suggestions.

Thank you,

Bill



Column A Column B

Fruits x
Vegetables
5
Meat x
6
Milk x
Bread
Beverage
Fish x
9
Lamb x
Soup




.