Help with a procedure to make things easier

B

Bill

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
 
G

Gary Keramidas

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
 
C

Chip Pearson

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]
 
B

Bill

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
 
B

Bill

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 said:
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]




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
Click to expand...
.
Click to expand...
 
C

Chip Pearson

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]



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 said:
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]




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
Click to expand...
.
Click to expand...
Click to expand...
 
G

Gary Keramidas

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I reshuffle some column data based on a coding scheme? 9
looking for a macro 2
Lookup or Match with rows and column together 5
Duplicates by groups 6
Reference a column to make groups 1
Problems with lists and dat validation 2
asign a number to a text string 7
Function to move data 1

Top