Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



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

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 13-05-2011
Member
 
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 06:27 PM.
Reply With Quote
  #2  
Old 14-05-2011
Member
 
Join Date: Mar 2010
Posts: 143
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.
Reply With Quote
  #3  
Old 14-05-2011
Member
 
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 04:33 PM.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Excel macro: Cells.Find finding value from a variable?"
Thread Thread Starter Forum Replies Last Post
Excel Macro Popup Message " Do you want to replace the contents of the destination cells" Darmody Windows Software 5 31-12-2011 05:16 PM
Where can I find excel style to get data input cells Krishnaraju Windows Software 5 06-07-2011 01:05 AM
How to find particular column name by using Excel Macro Eleder Windows Software 5 05-04-2011 11:49 PM
Cannot find macro working in excel 2007 Visala Windows Software 3 07-08-2009 12:47 AM
Excel macro variable to delete rows JPGargoyle Windows Software 2 22-06-2009 12:26 PM


All times are GMT +5.5. The time now is 03:51 AM.