View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
TomD TomD is offline
external usenet poster
 
Posts: 12
Default Column Sort (VBA)

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

"TomD" wrote in

message
...
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 .


-----Original Message-----
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" wrote in

message
...
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


.



.