Results 1 to 3 of 3

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

  1. #1
    Join Date
    May 2011
    Posts
    2

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

    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.
    Last edited by pkpn; 13-05-2011 at 05:27 PM.

  2. #2
    Join Date
    Mar 2010
    Posts
    145

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

    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.

  3. #3
    Join Date
    May 2011
    Posts
    2

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

    Quote Originally Posted by Mattabesic View Post
    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.
    Last edited by pkpn; 14-05-2011 at 03:33 PM.

Similar Threads

  1. Replies: 5
    Last Post: 31-12-2011, 04:16 PM
  2. Where can I find excel style to get data input cells
    By Krishnaraju in forum Windows Software
    Replies: 5
    Last Post: 06-07-2011, 12:05 AM
  3. How to find particular column name by using Excel Macro
    By Eleder in forum Windows Software
    Replies: 5
    Last Post: 05-04-2011, 10:49 PM
  4. Cannot find macro working in excel 2007
    By Visala in forum Windows Software
    Replies: 3
    Last Post: 06-08-2009, 11:47 PM
  5. Excel macro variable to delete rows
    By JPGargoyle in forum Windows Software
    Replies: 2
    Last Post: 22-06-2009, 11:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •