Find

[TomD]

I have a multi column spreadsheet dedicated to golf
scoring.

In one column I have either the value 1, 2 or Ladies.
These are the Divisions in the Golf Club. I have a search
of this column to provide the first 3 positions in each
Division and copy the associated name to a seperate sheet
for subsequent printing and posting on notice board.

Throughout the summer 1 never had a problem, but the
winter months produced hiccups. I've at last isolated the
problem

Basically, during winter not many Ladies play in
competitions (too Cold!!) and there may be only one or
two. I search for the first three positions. The search
does not appear to be limited to the one Column
(Division). Not finding "Ladies" in this column the
search moves to another column. In finding the
text "Ladies" it picks up incorrect date and not the
associated name as required.

How do I limited the search to one column only and not
the whole sheet. What code is required to implement to
enure I pick up correct data only i.e if only two
occurance in column, then only two copied, third position
left blank


Thanks in anticipation

TomD

 

[Dick Kusleika]

Tom

What is the code you are using now?

 

[TomD]

Dick,

Apologies. Meant to state in first message using VBA
code. This generally genereted via record macro though
some has been added by hand to enhance. I'm not a
programmer but have read up when require to do something.

Tom

 

[Dick Kusleika]

Tom

Post the code that you have now.

 

[TomD]

Dick,

Here it is....Remember, I'm no programmer, so if you see
a better more compact way to achieve please advise.

' First Position Ladies Section

Worksheets("Medal").Activate
ActiveSheet.Unprotect
Range("D6705").Select 'Select Division Column
Cells.Find(What:="Ladies", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell = "Ladies" Then
ActiveCell.Previous.Select
ActiveCell.Previous.Select
Selection.Copy
Sheets("Two's").Select
Range("E16").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Sheets("Two's").Select
Range("E16").Select
ActiveCell = ""
End If
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowFiltering:=True

' Second Position Ladies Section

Worksheets("Medal").Activate
ActiveSheet.Unprotect
Range("D6705").Select 'Select Division Column
Cells.Find(What:="Ladies", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
If ActiveCell = "Ladies" Then
ActiveCell.Previous.Select
ActiveCell.Previous.Select
Selection.Copy
Sheets("Two's").Select
Range("E17").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Sheets("Two's").Select
Range("E17").Select
ActiveCell = ""
End If
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowFiltering:=True

'Third Position Ladies Section

Worksheets("Medal").Activate
ActiveSheet.Unprotect
Range("D6705").Select 'Select Division Column
Cells.Find(What:="Ladies", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
If ActiveCell = "Ladies" Then
ActiveCell.Previous.Select
ActiveCell.Previous.Select
Selection.Copy
Sheets("Two's").Select
Range("E18").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Sheets("Two's").Select
Range("E18").Select
ActiveCell = ""
End If
Range("F1").Select
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowFiltering:=True

TomD

 

[Dick Kusleika]

TomD

I know it stinks to show the world your code, but it's worth it. Here's
what's happening

Range("D6705").Select 'Select Division Column
Cells.Find(What:="Ladies", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Using Cells without saying which cells assumes you want all cells in the
ActiveSheet. That's why if there weren't enough "Ladies" in D6705, it
would look outside that range. Having those cells selected doesn't limit
the find to just the selection

' Second Position Ladies Section

This repetition can be completed more efficiently in a loop. See below.

If I were to write this code, here's how I would do it (comments
interspersed so you can see what's going on.)

Sub ListLadies()

Dim DivRng As Range
Dim FndRng As Range
Dim ResultRng As Range
Dim LadyCount As Long
Dim FirstAdd As String

'Set up the range in which to look
Set DivRng = ThisWorkbook.Sheets("Medal").Range("D6705")

'define the range one cell above the range to which to write
Set ResultRng = ThisWorkbook.Sheets("Two's").Range("e15")

'Initialize the counter
LadyCount = 0

'Find the first instance of Ladies in DivRng
' after is set to the last cell in the range so it starts
' at the top
Set FndRng = DivRng.Find( _
what:="Ladies", _
after:=DivRng.Cells(DivRng.Cells.Count), _
lookat:=xlWhole)

'If no Ladies was found, then FndRng will be Nothing. If it
'was found, then this part will execute
If Not FndRng Is Nothing Then

'Store the address of the first cell found
FirstAdd = FndRng.Address

'Start a loop
Do
'Increment ladycount to show one more found
LadyCount = LadyCount + 1

'Set the value of Two's equal to the value 2 columns
'to the left of the found cell
ResultRng.Offset(LadyCount, 0).Value = FndRng.Offset(0, -2).Value

'Find the next cell
Set FndRng = DivRng.FindNext(FndRng)

'Stop the loop when the Find has gone back around to the beginning
'or when we've already found 3 ladies
Loop While FndRng.Address <> FirstAdd And LadyCount < 3

End If

End Sub

If you want further explanation on any of the above, you have but to ask.

 

[TomD]

Dick,

Firstly, many thanks for your help on this. As stated,
I'm no programmer, so I'll need time to understand your
response.......I've an Excel 2000 VBA Programmers
Reference Manual to help me with this.

I have to disagree with your opening statement. If your
good, show the world your good as it opens up all sorts
of avenue's and opportunities. Without people like you,
people like me (dare I say it...old and retired) would be
struggling. I'm truely grateful for your help.

Should I have problems, I'll repost under Find (2)

Tom