I am trying to record a macro in excel that will allow me to copy the cell that I am on then paste it into find. Every time I try it remembers what the value that was copied when I recorded the macro. I have tried the relative and absolute button but this has not helped. I always get the same result in the editor.
Selection.Copy Cells.Find(What:="200260", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub
Sub fnd() Dim Found As Range Set Found = ActiveSheet.UsedRange.Find(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Found Is Nothing Then MsgBox "Not found" Else Application.Goto reference:=Found, scroll:=True End If End Sub
Thank you VoG What I need to be able to do is change the "What" to "paste from clipboard".
What the whole macro should do is
1 Copy the cell I am on in a worksheet called "Sales" 2 move to a worksheet (in the same workbook) called Auctions. 3 Open the find dialogue box and paste in the value. Find next. 4 Move 2 columns right 5 Cut the value in the cell 6 move to "Sales" worksheet 7 Paste in Value from "auctions" Worksheet. 8 Move down one row ready for activating the macro again.
I have it working except for the paste in value in the find dialogue.
Try this - you don't need top copy anything, just select the relevant cell on the Sales sheet and run the macro.
[code] Sub fnd() Dim Found As Range, Where As String, ToFind As Variant ToFind = ActiveCell.Value Where = ActiveCell.Address With Sheets("Auctions") Set Found = .UsedRange.Find(What:=ToFind) If Found Is Nothing Then MsgBox "Not found" Else Found.Offset(, 2).Cut Destination:=Sheets("Sales").Range(Where) Application.CutCopyMode = False Range(Where).Offset(1).Select End If End With End Sub [/code]