Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Getting Macro To Find Cell Value

  1. #1
    Join Date
    15th February 2007
    Posts
    3

    Getting Macro To Find Cell Value

    Hello all,

    I was wonding if someone can help me with a macro problem that has been driving me crazy. What i want to do is to have excel search for a number that i specify and input a certain number adjacent to it. So...

    45
    46
    47 28
    48
    49
    50

    i would specify excel to search number 47 and have it input 28 next to it, and continue to do this for a list of numbers.
    VB:
    Range("A1804").Select 
    ActiveCell.FormulaR1C1 = "47" 
    Cells.Find(What:="47", After:=ActiveCell, LookIn:=xlFormulas, _ 
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
    MatchCase:=False, SearchFormat:=False).Activate 
    Range("B1804").Select 
    Selection.Copy 
    Range("F1791").Select 
    ActiveSheet.Paste 
    End Sub 
    
    
    I need it to search the cell value of A1804 because it will change often and need it to continue the process to A1805,A1806, .....and so on. The macro i created will only search for 47 and will not move to the next line. I know this is a bit confusing and difficult to understand, so any help you can give would be great.
    Last edited by ByTheCringe2; February 15th, 2007 at 15:30.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th February 2007
    Location
    Melbourne - Australia
    Posts
    156

    Re: Getting Macro To Find Cell Value

    Your problem description is lacking in detail to give a complete answer

    The following code should help to point you in the right direction.

    VB:
    Dim RngLook4 As Range 
    Dim rngFound As Range 
    For Each RngLook4 In Range("a1804:a1810") 
        Set rngFound = Cells.Find(What:=RngLook4.Value, After:=RngLook4, LookIn:=xlFormulas, _ 
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False) 
        If Not rngFound Is Nothing Then 
            Range("f1791").Value = RngLook4.Value 
        End If 
    End Sub 
    Next RngLook4 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Getting Macro To Find Cell Value

    Vbarunner, Welcome to the forum. However, please read the rules and use code tags for VBA. I have added them for you this time. Normally the post would be deleted.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th July 2004
    Posts
    1,330

    Re: Getting Macro To Find Cell Value

    this is one approach

    VB:
    Sub fndadd() 
        Dim myrange, mycell As Range 
        Dim a, b As Long 
        On Error Resume Next 
        Set myrange = Sheets("sheet1").Range("A1", Range("A65536").End(xlUp)) 
        a = InputBox("Search Number") 
        b = InputBox("My value") 
        For Each mycell In myrange 
            If mycell.Value = Val(a) Then mycell.Offset(0, 1) = Val(b) 
        Next mycell 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th February 2007
    Posts
    3

    Re: Getting Macro To Find Cell Value

    Sorry Bythecringe2, I will do it next time and read the forum rules again more carefully. Thanks for the input, ill try these and let you guys know how it goes. Might i say im surprised that i even got a response. If i did not write it mysef i would not even know what im talking about j/k . Thanks, again.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th February 2007
    Posts
    3

    Re: Getting Macro To Find Cell Value

    Quote Originally Posted by mudraker
    Your problem description is lacking in detail to give a complete answer

    The following code should help to point you in the right direction.

    VB:
    Dim RngLook4 As Range 
    Dim rngFound As Range 
    For Each RngLook4 In Range("a1804:a1810") 
        Set rngFound = Cells.Find(What:=RngLook4.Value, After:=RngLook4, LookIn:=xlFormulas, _ 
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False) 
        If Not rngFound Is Nothing Then 
            Range("f1791").Value = RngLook4.Value 
        End If 
    End Sub 
    Next RngLook4 
    
    

    I think this is the code i want to use. However i am new to VBA and when i copied the code over to the module it does nothing. I replace the entire recorded macro from before.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Macro Find Empty Cell In Row Above
    By abundant_lyfe in forum EXCEL HELP
    Replies: 8
    Last Post: May 17th, 2013, 20:15
  2. Replies: 9
    Last Post: December 14th, 2010, 23:41
  3. Macro To Find Cell Value In Column
    By pschreibs1 in forum EXCEL HELP
    Replies: 7
    Last Post: June 19th, 2008, 04:50
  4. Replies: 11
    Last Post: April 3rd, 2007, 09:48
  5. macro to find cell delete 5 lines ...
    By WendyB in forum EXCEL HELP
    Replies: 2
    Last Post: March 5th, 2005, 01:21

Bookmarks

Posting Permissions

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