Excel VB macro to find values in worksheet

  woollardo 11:43 07 Mar 2008

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

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


  VoG II 11:56 07 Mar 2008

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
Application.Goto reference:=Found, scroll:=True
End If
End Sub

  woollardo 12:01 07 Mar 2008

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.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?