StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POImproving efficiency with nested for loops & array comparisons
primarykey
Id
9414856
data
AcceptedAnswerId
9415084
AnswerCount
3
ClosedDate
CommentCount
6
CommunityOwnedDate
CreationDate
2012-02-23T14:22:17.773
FavoriteCount
0
LastActivityDate
2012-02-24T14:33:26.760
LastEditDate
2012-02-23T14:27:27.273
LastEditorUserId
1128875
OwnerUserId
1128875
ParentId
0
PostTypeId
1
Score
1
ViewCount
1614
LastEditorDisplayName
text
Body
<p>I have a macro that loops across two sheets comparing words in each cell. The code works fine, but is there a way to improve the efficiency or speed of this? I manually used a for loop to compare the string arrays as well, as I didn't find any VBA functions to do this. I do have ScreenUpdating off, which seems to help a bit.</p> <pre><code>For i = 2 To maxMn 'loop 1 Sheets("Sh1").Select Cells(i, 2).Select mnStr = Cells(i, 2).Value mnArr = Split(mnStr, " ") x = 2 For x = 2 To maxNm 'loop 2 numTotal = 0 numMatches = 0 Sheets("Sh2").Select Cells(x, 6).Select nameStr = Cells(x, 6).Value nameArr = Split(nameStr, " ") For Each mn In mnArr 'loop 3 For Each nam In nameArr 'loop 4 Application.StatusBar = "(#" & i & " Sh1) (#" & x & " Sh2): Comparing " & mn & " to " & nam If LCase(nam) = LCase(mn) Then 'MsgBox "Yes, '" & nam & "' equal to " & mn numMatches = numMatches + 1 Else 'MsgBox "No, '" & nam & "' does not equal " & mn End If Next nam '4: For Each nam In nameArr numTotal = numTotal + 1 Next mn '3: For Each mn In mnArr If numTotal > 2 And numTotal > 0 And numMatches >= numTotal / 2 Then LogMsg = "(#" & i & " Sh1) (#" & x & " Sh2): |" & mnStr & "| - |" & nameStr & "| = " & numMatches & "/" & numTotal & " matches." Print #FileNum, LogMsg 'MsgBox numMatches & " matches out of " & numTotal & " total." End If Next x '2: For x = 2 To maxNm Next i '1: For i = 2 To maxMn </code></pre>
Tags
<performance><excel><vba>
Title
Improving efficiency with nested for loops & array comparisons
singulars
PostAcceptedAnswerId
1. PO
  singulars
  PostTypePostTypeId
  PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USJBurace
UserOwnerUserId
1. USJBurace
plurals
PostLinksPostIdRelatedPostId
1. PL
  singulars
  LinkTypeLinkTypeId
  LTLinked
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
  singulars
  PostTypePostTypeId
  PTAnswer
2. PO
  singulars
  PostTypePostTypeId
  PTAnswer
3. PO
  singulars
  PostTypePostTypeId
  PTAnswer
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  POImproving efficiency with nested for loops & array comparisons
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.