I am trying to make a macro in excel 2007 that has to copy a value from a list in a worksheet and then go to another worksheet and search for this value, if there take another value from this row and return that value to a cell next to the cell I originally came from getting the first value.
My macro so far looks like this:
Code:
Sub Antal()
ActiveCell.Offset(1, 1).Range("A1").Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Cells.Find(What:="17657100", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 8).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
End Sub
This works but it will always return "17657100" as my value even though I start the macro from another place to find another value.
Is it possible to save the value from Selection.Copy into a variable and then make Cells.Find search for this data, perhaps like this:
Code:
Sub Antal()
ActiveCell.Offset(1, 1).Range("A1").Select
Selection.Copy
varVareNr = Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Cells.Find(What:=varVareNr, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 8).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
End Sub
However this doesn't work. :/
Can someone please help me out here as I otherwise have loads of lines to manually edit.
Bookmarks