Why code for find() is not working
 

Hello Anyone:
Best wishes. I have an excel report sheet that contains 3 sets of data
in tabular form. For each set, I will find maximum value and will
highlight the cell. This maximum value is in columns b,d,f,h and j. I
am selecting each set of data as sepatre range and finding and
highlighting my cell.
----------code
range1.select
with selection
set
fndcell=cells.find(what:=val,after:=activecell,loo kin:=xlvalues,lookat:=xlpart(orxlwhole),searchorde r:=xlbycolumns,searchdirection:=xlnext,matchcase:= true).
end with
problem:
I have a max value in both 1st and 2nd set.(say 500). In my 2nd loop,
though I am selecting 2nd range, its finding the 500 max value from
range1(for set1 data) and highlighting it. The range2 selection is fine
but .find() is going outside the selection and trying to see the data
there.
what is the wrong in my code.Is the problem in the find() cluase?
Thanks in advance


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=496471

Why code for find() is not working
 

It's probably best to copy the code directly from the VBE and paste into your
message. Lots of stuff can get lost if you just type in the compose window (of
excelforum).

You've got a couple of typos in that line and it's difficult to guess whether
the typo is in your code or just in your post.

set fndcell = .cells... (notice the leading dot)

the lookat:= is one of those--not what you posted.

But this kind of code worked ok for me:

Option Explicit
Sub HighlightMax()

Dim myRng As Range
Dim wks As Worksheet
Dim myMax As Double
Dim myCol As Range
Dim FoundCell As Range
Set wks = ActiveSheet

With wks
Set myRng = .Range("b1,d1,f1,h1,j1").EntireColumn
For Each myCol In myRng.Columns
With myCol
myMax = Application.Max(.Cells)
Set FoundCell = .Find(what:=myMax, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext)
If FoundCell Is Nothing Then
'do nothing (all text???)
Else
FoundCell.Interior.ColorIndex = 6
End If
End With
Next myCol
End With

End Sub


jesmin wrote:

Hello Anyone:
Best wishes. I have an excel report sheet that contains 3 sets of data
in tabular form. For each set, I will find maximum value and will
highlight the cell. This maximum value is in columns b,d,f,h and j. I
am selecting each set of data as sepatre range and finding and
highlighting my cell.
----------code
range1.select
with selection
set
fndcell=cells.find(what:=val,after:=activecell,loo kin:=xlvalues,lookat:=xlpart(orxlwhole),searchorde r:=xlbycolumns,searchdirection:=xlnext,matchcase:= true).
end with
problem:
I have a max value in both 1st and 2nd set.(say 500). In my 2nd loop,
though I am selecting 2nd range, its finding the 500 max value from
range1(for set1 data) and highlighting it. The range2 selection is fine
but .find() is going outside the selection and trying to see the data
there.
what is the wrong in my code.Is the problem in the find() cluase?
Thanks in advance

--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=496471

--

Dave Peterson