Announcement

Collapse
No announcement yet.

Getting Macro To Find Cell Value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.
    Code:
        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, 16:30.

  • #2
    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.

    Code:
    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

    Comment


    • #3
      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.
      .

      Comment


      • #4
        Re: Getting Macro To Find Cell Value

        this is one approach

        Code:
        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

        Comment


        • #5
          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.

          Comment


          • #6
            Re: Getting Macro To Find Cell Value

            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.

            Code:
            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.

            Comment

            Working...
            X