need macro to automatically search for cell contents after cell is changed

I'm no VBA expert, but this might help get you started. I'm assuming that the cell you are entering your value in is A1 and that you will have only one match to what is entered, or you only need to find the first match...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Cells.Find(What:=Range("A1").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
End If
End Sub

I am unable to get this to work. I inserted your code by right-clicking on the icon next to the file menu, and selecting view code. Then I pasted the code there and saved the file. When I type data in cell A1, it is not running the macro. Am I doing something wrong?

Thanks,
Kevin

What would the code be if I wanted to search only one column?

Regards

Lewis

To search column B for first match of value in A1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B:B")
Selection.Find(What:=Range("A1").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
End If
End Sub

Kind of funky code. Perhaps a better VBA'er can clean it up?

The macro needs to be placed on the worksheet that you want it to act on. Hit Alt-F11 to view code. While in the VB Application, you should have a window titled something like "Project-VBA Project". If not, hit ctrl-R to open that window. You'll notice that there will be one or more objects in that window that start with VBAProject. Look for the one that has the workbook title you are working with and double-click on the sheet that you are working with in the workbook. Then paste the code there. Save the workbook and retry the operation. Let me know if that solves it for you... I am unable to get this to work. I inserted your code by right-clicking on the icon next to the file menu, and selecting view code. Then I pasted the code there and saved the file. When I type data in cell A1, it is not running the macro. Am I doing something wrong?

Still unable to get this to work for me

I insert the code, then go to the sheet and type something in A1 and press enter. Nothing happens. It does not search for the contents of the cell in column B or anywhere as far as I can tell. Is there a step that I am missing, because in my original post, I wanted a macro that would automatically run as soon as you edited your cell contents.

Thanks,
Kevin

Re: Still unable to get this to work for me

Hmm... Which version of Excel are you using? I'm on 97. If you are willing to send me the workbook @ darag2358@yahoo.com, I can certainly take a look (be sure to save down to 97, if you are using 2000 or higher). The macro I gave you is supposed to work as soon as you enter a value in cell A1 and hit enter, then search the sheet for the first match. If it returns back to the cell A1, then there is no match. I insert the code, then go to the sheet and type something in A1 and press enter. Nothing happens. It does not search for the contents of the cell in column B or anywhere as far as I can tell. Is there a step that I am missing, because in my original post, I wanted a macro that would automatically run as soon as you edited your cell contents.

I modified the code slightly and now it works

I am using Excel 2000 - maybe that is why. Here is the code (the first line is slightly different):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
Range("A1").Select
If ActiveCell.Address = Target.Address Then MsgBox ("No Matching ESN Found")
End If
End Sub

Additional question regarding this macro

If I could, I would like to take this macro even one step further. After the macro finds a matching value and goes to it, how could I make it go over three cells to the right, and have it automatically add 1 to whatever the value was in that cell?

Re: Additional question regarding this macro

Try replacing the second If statement (I like what you did there, by the way...) to:

If ActiveCell.Address = Target.Address Then
MsgBox ("No Matching ESN Found")
Else
ActiveCell.Offset(0, 3).Select
Selection.Value = Selection.Value + 1
End If If I could, I would like to take this macro even one step further. After the macro finds a matching value and goes to it, how could I make it go over three cells to the right, and have it automatically add 1 to whatever the value was in that cell?