Excel macro: Cells.Find finding value from a variable?

[pkpn]

Sponsored Links

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:

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:

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.

[Mattabesic]

Depends on how the value has changed and what pattern of changes is possible. If you manually edit the cell, then you can use the Change sheet event and trap for the argument indicating the change target shooting was in cell A1. This would work for a change DDE generated in A1 and you are using Excel 2000 or later. If the cell is altered to a formula that is recalculated, you cannot catch the alteration is explicit in A1, but you can check the value of A1 in each calculation (store the current value for the next) and then if you change, take action. Use a static variable or a public variable in the case Spreadsheet.

[pkpn]

Originally Posted by Mattabesic

Depends on how the value has changed and what pattern of changes is possible. If you manually edit the cell, then you can use the Change sheet event and trap for the argument indicating the change target shooting was in cell A1. This would work for a change DDE generated in A1 and you are using Excel 2000 or later. If the cell is altered to a formula that is recalculated, you cannot catch the alteration is explicit in A1, but you can check the value of A1 in each calculation (store the current value for the next) and then if you change, take action. Use a static variable or a public variable in the case Spreadsheet.

Thank you for your response. However I didn't understand anything from what you just wrote.
I am new to all this VB scripting and the macro I have I just recorded using the macro recorder in Excel 2007.

I want to be able to make the script/macro search something in another sheet and return another value next to the found value (8 boxes to the right of the found value) - the returned value should then be pasted in a box left to where I originally got the value from I searched for.

And by the way all the data in the fields are static - there's no formula to alter them. This is just a macro to copy/paste the values into other fields.