Excel VB macro to find values in worksheet

  woollardo 11:43 07 Mar 08
Locked

I don't really know any VB and on the odd occasion I need a macro usually manage with the record facility. However, I recorded a macro in Excel 2003 to find the value contained in the current cell (in this case an order number) in another worksheet in the same workbook. The intention of the macro is to copy and paste what is in the current cell into the "find what" box in Excel's find function. Unfortunately, when I run the macro again it always searches for the same value. How do change the VB to use the copied value of a cell rather than the fixed value that shows i.e. the "What :=" should be the copied value.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/03/2008 by acc100
'

'
Selection.Copy
Sheets("Orders raised").Select
Cells.Find(What:="68126484", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Application.CutCopyMode = False
End Sub

Thanks

  VoG II 11:56 07 Mar 08

The macro recorder is very useful but it records literally what you have done as you have discovered. Try this instead.

Sub Finder()
Dim FindMe, Found As Range
FindMe = ActiveCell.Value
Set Found = Sheets("Orders raised").Cells.Find(What:=FindMe)
If Found Is Nothing Then
MsgBox FindMe & " not found", vbExclamation
Else
Application.Goto reference:=Found, scroll:=True
End If
End Sub

  woollardo 12:01 07 Mar 08

Thanks so very much. It works just as I want it to. I'm very grateful ....

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5