Excel Macro problems

  Scott_the_Scot 19:01 31 Oct 2008
Locked

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

Any help would be greatly apreciated

  VoG II 19:12 31 Oct 2008

Do you mean that you want to change the "what" from "200260" to something else?

  VoG II 20:13 31 Oct 2008

Perhaps


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

  Scott_the_Scot 16:00 01 Nov 2008

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.

  VoG II 17:12 01 Nov 2008

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]

  Scott_the_Scot 18:26 01 Nov 2008

Thank you VoG
This does exactly what I need.

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

Elsewhere on IDG sites

OnePlus 5 review

Best of the Grad Shows 2017: UAL Central Saint Martins

MacBook Pro 15-inch (2017) review

Comment connecter un MacBook à une TV ?