Find a specific cell in a row and copy and paste value into a given range of its column.

  • Hello,


    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question


    I'm trying to make a code that will search in row 11 of a spreadsheet until it find a specific cell that will be written with the word "ok". Once this cell is found, the macro should copy and paste a value in a certain range of this column. from line 50 to 586 of this column.


    I am not very familiar with VBA but I would be very grateful for your help.



    Many thanks in advance!!:)

  • Where do you want to paste the data?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)


    This should also work:

    Code
    1. Sub PasteValues()
    2. Application.ScreenUpdating = False
    3. Dim fnd As Range
    4. Set fnd = Rows(11).Find("ok", LookIn:=xlValues, lookat:=xlWhole)
    5. If Not fnd Is Nothing Then
    6. Range(Cells(50, fnd.Column), Cells(586, fnd.Column)).Value = Range(Cells(50, fnd.Column), Cells(586, fnd.Column)).Value
    7. End If
    8. Application.CutCopyMode = False
    9. Application.ScreenUpdating = True
    10. End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.