RE: [MS_AccessPros] OFF TOPIC: MS Excel VBA code

 

It works fine for me, no compile errors. When you copy/pasted it, did you get lots of intervening blanks lines as the list shows? If so, delete them.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 16 February 2016 14:03
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] OFF TOPIC: MS Excel VBA code

 

 

Thank you Bob. The code gets hung up at the start of the ".Sort.SortFields.AddKey:=.Cells(i, "A").Resize(, 15), _"
saying that their is a compile error/syntax error.

.Sort.SortFields.Add Key:=.Cells(i, "A").Resize(, 15), _

SortOn:=xlSortOnValues, _

Order:=xlAscending, _

DataOption:=xlSortNormal

.Sort.SetRange .Cells(i, "A").Resize(, 15)

.Sort.Header = xlGuess

.Sort.MatchCase = False

.Sort.Orientation = xlLeftToRight

.Sort.SortMethod = xlPinYin

.Sort.Apply

Next i

End With

---------------------------
Here is a snippet of what I have in Sheet2 of Excel before running the macro:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 header
A A
A A H
A A H E D
A A H I N G
A A H S
A A L
A A L I I
A A L I I S
A A L S
A A R D V A R K
A A R D V A R K S
A A R D W O L F
A A R D W O L V E S

Thank you for your help

Eric Lutz

Application.ScreenUpdating = True

End Sub
--------------------------------------------
On Tue, 2/16/16, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Subject: RE: [MS_AccessPros] OFF TOPIC: MS Excel VBA code
To: MS_Access_Professionals@yahoogroups.com
Date: Tuesday, February 16, 2016, 6:31 AM


 









Sub
ScrabbleSort()'' ScrabbleSort
Macro' sorting
letters in a word alphabetically'  Dim lastrow As
LongDim i As
Long     
Application.ScreenUpdating = False        With
ActiveWorkbook.Worksheets("Sheet3")            lastrow
= .Cells(.Rows.Count,
"A").End(xlUp).Row        For i
= 2 To lastrow       
            .Sort.SortFields.Clear           
.Sort.SortFields.Add Key:=.Cells(i, "A").Resize(,
15), _                                
SortOn:=xlSortOnValues, _                                
Order:=xlAscending, _                                
DataOption:=xlSortNormal           
.Sort.SetRange .Cells(i, "A").Resize(,
15)           
.Sort.Header = xlGuess           
.Sort.MatchCase = False           
.Sort.Orientation = xlLeftToRight           
.Sort.SortMethod = xlPinYin           
.Sort.Apply        Next
i    End
With     
Application.ScreenUpdating = TrueEnd Sub
 From:
MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 16 February 2016 03:12
To:
MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] OFF TOPIC: MS
Excel VBA code   
I could not
find the yahoo MS Excel group email so I am posting here.

I grabbed a list of dictionary
words, from 2 to 15 letters long. Each word gets its own row
in Excel. Each letter of each word gets its own column in
Excel (I parsed the words out via text to columns). Since
these words can be up to 15 letters in length that means it
is possible to go out to column O in Excel.

I want to take each word in
each row and turn it into its "alphagram"
An alphagram is putting each word's letters
in alphabetical sort order.
For instance,
AARDVARK, would be sorted to AAADKRRV

I have the following VBA code, but it would be
real tedious to repeat the same code for each and row up to
187,633 rows of words plus one header row, hence why code
starts at row 2.

Is there
an easier way? Code is below:

Thank you in advance for any help you can
give.

Eric Lutz
---------------------------------------------------

Sub ScrabbleSort()
'
' ScrabbleSort
Macro
' sorting letters in a word
alphabetically
'

'
Rows("2:2").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A2:O2"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A2:O2")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("3:3").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A3:O3"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A3:O3")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("4:4").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A4:O4"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A4:O4")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("5:5").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A5:O5"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A5:O5")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("6:6").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A6:O6"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A6:O6")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("7:7").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A7:O7"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A7:O7")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("8:8").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A8:O8"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A8:O8")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("9:9").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A9:O9"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A9:O9")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("10:10").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A10:O10") _
,
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A10:O10")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("11:11").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A11:O11") _
,
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A11:O11")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("12:12").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A12:O12") _
,
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A12:O12")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("13:13").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A13:O13") _
,
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A13:O13")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("14:14").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A14:O14") _
,
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A14:O14")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("15:15").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A15:O15") _
,
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A15:O15")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("16:16").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A16:O16") _
,
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A16:O16")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Rows("17:17").Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add
Key:=Range("A17:O17") _
,
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A17:O17")
.Header = xlGuess
.MatchCase =
False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub










#yiv0660285407 #yiv0660285407 --
#yiv0660285407ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv0660285407 #yiv0660285407ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv0660285407 #yiv0660285407ygrp-mkp #yiv0660285407hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv0660285407 #yiv0660285407ygrp-mkp #yiv0660285407ads {
margin-bottom:10px;}

#yiv0660285407 #yiv0660285407ygrp-mkp .yiv0660285407ad {
padding:0 0;}

#yiv0660285407 #yiv0660285407ygrp-mkp .yiv0660285407ad p {
margin:0;}

#yiv0660285407 #yiv0660285407ygrp-mkp .yiv0660285407ad a {
color:#0000ff;text-decoration:none;}
#yiv0660285407 #yiv0660285407ygrp-sponsor
#yiv0660285407ygrp-lc {
font-family:Arial;}

#yiv0660285407 #yiv0660285407ygrp-sponsor
#yiv0660285407ygrp-lc #yiv0660285407hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv0660285407 #yiv0660285407ygrp-sponsor
#yiv0660285407ygrp-lc .yiv0660285407ad {
margin-bottom:10px;padding:0 0;}

#yiv0660285407 #yiv0660285407actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv0660285407 #yiv0660285407activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv0660285407 #yiv0660285407activity span {
font-weight:700;}

#yiv0660285407 #yiv0660285407activity span:first-child {
text-transform:uppercase;}

#yiv0660285407 #yiv0660285407activity span a {
color:#5085b6;text-decoration:none;}

#yiv0660285407 #yiv0660285407activity span span {
color:#ff7900;}

#yiv0660285407 #yiv0660285407activity span
.yiv0660285407underline {
text-decoration:underline;}

#yiv0660285407 .yiv0660285407attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv0660285407 .yiv0660285407attach div a {
text-decoration:none;}

#yiv0660285407 .yiv0660285407attach img {
border:none;padding-right:5px;}

#yiv0660285407 .yiv0660285407attach label {
display:block;margin-bottom:5px;}

#yiv0660285407 .yiv0660285407attach label a {
text-decoration:none;}

#yiv0660285407 blockquote {
margin:0 0 0 4px;}

#yiv0660285407 .yiv0660285407bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv0660285407 .yiv0660285407bold a {
text-decoration:none;}

#yiv0660285407 dd.yiv0660285407last p a {
font-family:Verdana;font-weight:700;}

#yiv0660285407 dd.yiv0660285407last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv0660285407 dd.yiv0660285407last p
span.yiv0660285407yshortcuts {
margin-right:0;}

#yiv0660285407 div.yiv0660285407attach-table div div a {
text-decoration:none;}

#yiv0660285407 div.yiv0660285407attach-table {
width:400px;}

#yiv0660285407 div.yiv0660285407file-title a, #yiv0660285407
div.yiv0660285407file-title a:active, #yiv0660285407
div.yiv0660285407file-title a:hover, #yiv0660285407
div.yiv0660285407file-title a:visited {
text-decoration:none;}

#yiv0660285407 div.yiv0660285407photo-title a,
#yiv0660285407 div.yiv0660285407photo-title a:active,
#yiv0660285407 div.yiv0660285407photo-title a:hover,
#yiv0660285407 div.yiv0660285407photo-title a:visited {
text-decoration:none;}

#yiv0660285407 div#yiv0660285407ygrp-mlmsg
#yiv0660285407ygrp-msg p a span.yiv0660285407yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv0660285407 .yiv0660285407green {
color:#628c2a;}

#yiv0660285407 .yiv0660285407MsoNormal {
margin:0 0 0 0;}

#yiv0660285407 o {
font-size:0;}

#yiv0660285407 #yiv0660285407photos div {
float:left;width:72px;}

#yiv0660285407 #yiv0660285407photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv0660285407 #yiv0660285407photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv0660285407 #yiv0660285407reco-category {
font-size:77%;}

#yiv0660285407 #yiv0660285407reco-desc {
font-size:77%;}

#yiv0660285407 .yiv0660285407replbq {
margin:4px;}

#yiv0660285407 #yiv0660285407ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv0660285407 #yiv0660285407ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv0660285407 #yiv0660285407ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv0660285407 #yiv0660285407ygrp-mlmsg select,
#yiv0660285407 input, #yiv0660285407 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv0660285407 #yiv0660285407ygrp-mlmsg pre, #yiv0660285407
code {
font:115% monospace;}

#yiv0660285407 #yiv0660285407ygrp-mlmsg * {
line-height:1.22em;}

#yiv0660285407 #yiv0660285407ygrp-mlmsg #yiv0660285407logo {
padding-bottom:10px;}


#yiv0660285407 #yiv0660285407ygrp-msg p a {
font-family:Verdana;}

#yiv0660285407 #yiv0660285407ygrp-msg
p#yiv0660285407attach-count span {
color:#1E66AE;font-weight:700;}

#yiv0660285407 #yiv0660285407ygrp-reco
#yiv0660285407reco-head {
color:#ff7900;font-weight:700;}

#yiv0660285407 #yiv0660285407ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv0660285407 #yiv0660285407ygrp-sponsor #yiv0660285407ov
li a {
font-size:130%;text-decoration:none;}

#yiv0660285407 #yiv0660285407ygrp-sponsor #yiv0660285407ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv0660285407 #yiv0660285407ygrp-sponsor #yiv0660285407ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv0660285407 #yiv0660285407ygrp-text {
font-family:Georgia;}

#yiv0660285407 #yiv0660285407ygrp-text p {
margin:0 0 1em 0;}

#yiv0660285407 #yiv0660285407ygrp-text tt {
font-size:120%;}

#yiv0660285407 #yiv0660285407ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv0660285407

__._,_.___

---

Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___