| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have a problem with "Find" code
On Aug 18, 3:06*pm, Joel wrote:
* * * * set c = *Selection.Find(What:=searchtext, After:=ActiveCell, _ * * * * * *LookIn:=xlFormulas, _ * * * * * *LookAt:=xlPart, SearchOrder:=xlByRows, * * * * * *SearchDirection:=xlNext, _ * * * * * *MatchCase:=False, SearchFormat:=False) * * * * if not c is nothing then * * * * * * * * With c.Interior * * * * * * * * * * .ColorIndex = 36 * * * * * * * * * * .Pattern = xlSolid * * * * * * * * End With * * * * *else * * * * * * msgbox ("did not find " & searchtext) * * * * *end if "Arlette" wrote: I need to lookup the values in column A with columns B to F, one at a time. *I put in a code which looks up each value in each column and highlights it in yellow. *It works great when it finds the value. However, the problem lies when it doesnt find the value in the column. I get a runtime error - error 91 saying "Object variable or with variable not set". The code is given below: Dim lastrow As Long, i As Integer, x As Integer, loc As Variant, searchtext As Variant Public Sub validate_names() Sheets("Buyer Names").Select Range("A1").Select Selection.End(xlDown).Select lastrow = ActiveCell.Row loc = "" For i = 2 To lastrow * * *For x = 2 To 6 * * * * searchtext = Range("A" & i).Text * * * * Range(searchtext).Select * * * * Selection.Copy * * * * Columns(x).Select * * * * With Selection.Find(What:=searchtext, After:=ActiveCell, LookIn:=xlFormulas, _ * * * * * * LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ * * * * * * MatchCase:=False, SearchFormat:=False).Activate * * * * * * loc = ActiveCell.Address * * * * * * If loc < "" Then * * * * * * * * Range(loc).Select * * * * * * * * With Selection.Interior * * * * * * * * * * .ColorIndex = 36 * * * * * * * * * * .Pattern = xlSolid * * * * * * * * End With * * * * * * End If * * * * *End With * * Next * * * * Range("A" & i).Select * * * * With Selection.Interior * * * * * * .ColorIndex = 36 * * * * * * .Pattern = xlSolid * * * * End With Next End Sub Please help me. *Your help is greatly appreciated.- Hide quoted text - - Show quoted text - Thank you so much...it really helped... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Find" function question - No code required | Excel Discussion (Misc queries) | |||
| Whats wrong with this? MyWBAccRep.Sheets("Volumes").Cells.Find("latest").Copy.Offset(0, | Excel Programming | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| VBA code to display the "Find" (Ctrl+F) dialog box | Excel Programming | |||
![Arlette[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
