Column Sort (VBA)

[TomD]

Consider a single Column of up to 80 rows. Each row
shows a numeric (1 or 2) or text value ("Ladies"). I'm
trying to find the first "n" values of each (say 6) and
copy associated names (2 columns to left) to a seperate
sheet.

I've had support on this code previously (thanks Dick)
but we seem to have run up against a brick wall on one
specific aspect.

The VBA code works............but only if the cells
contain the physical numeric or text. In reality, the
cell contains a formula that produces the numeric (1 or
2) or the text ("Ladies"). Doing a search where the cell
contains a formula does not find the numeric or text that
is displayed for all to see.

Is there a way to overcome this, dare I say,
deficiency ????

Thanks in anticipation

 

[Sue Harsevoort]

Since you didn't show your code, I am not sure what you are using, but if I
record a macro and do a find and change it to look at values instead of
formulas I get this code:

Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

So if you are doing something similar to this in your code, you should make
sure that you have LookIn:=xlValues to have it search the value that you see
displayed.

Sue

 

[TomD]

Sue,

Thanks for feedback

The relevant code is......... (Curtsey of Dick)

Dim DivRng1 As Range
Dim FndRng1 As Range
Dim ResultRng1 As Range
Dim Division1 As Long
Dim FirstAdd1 As String

Set FndRng1 = DivRng1.Find( _
what:="1", _
after:=DivRng1.Cells(1), _
lookat:=xlWhole)

'If no (Division) "1" was found, then FndRng will be
Nothing. If it was found, then this part will execute
'
If Not FndRng1 Is Nothing Then
'
'Store the address of the first cell found
'
FirstAdd1 = FndRng1.Address
'
'Start a loop
'
Do
'
'Increment Division count to show one more found
'
Division1 = Division1 + 1
'
'Set the value of Two's equal to the value 2 columns
'to the left of the found cell .

 

[Sue Harsevoort]

I think if you add the LookIn:=xlValues to this line:

Set FndRng1 = DivRng1.Find( _
what:="1", _
after:=DivRng1.Cells(1), _
lookat:=xlWhole)

and any other line that is doing a find it should fix your problem.

Sue

 

[Tomd]

Sue

I'll give it a go. Thanks for help.

Tom

-----Original Message-----
I think if you add the LookIn:=xlValues to this line:

Set FndRng1 = DivRng1.Find( _
what:="1", _
after:=DivRng1.Cells(1), _
lookat:=xlWhole)

and any other line that is doing a find it should fix your problem.

Sue




.