Results 1 to 7 of 7

Thread: Find and past value

  1. #1

    Find and past value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I came across this code ( which I've customized) that searches and offsets a specified name.
    How can this be changed to Copy/Paste the value instead of Cut?

    I'd like to keep this name in both columns,

    Code:
    Sub Offset_Name()
        Dim r       As Range
    
        Do
            Set r = Columns(4).Find("*-")
            If r Is Nothing Then Exit Do
            r.Cut Destination:=r.Offset(0, 1)
        Loop
    End Sub

    Thanks

  2. #2
    Change Cut to Copy

  3. #3
    Thanks, Bob

    Tried that, It copies the first cell, but then excel just hangs.


    any ideas?

  4. #4
    It is probably something to do with losing context, but not sure what. Can you post your workbbook?

  5. #5
    Offset_Test.xlsm

    Thanks Bob.
    My worksheet is just a simple test page, which works with the code as is, but is unable to copy them.

    Cheers

  6. #6
    Obvious when I see it

    Code:
    Sub Offset_Name()
    Dim r As Range
    Dim firstaddress As String
        Application.ScreenUpdating = False
        
        Set r = Columns(4).Find("*-")
        If Not r Is Nothing Then
        
            firstaddress = r.Address
            Do
                
                r.Copy Destination:=r.Offset(0, 1)
                Set r = Columns(4).FindNext(r)
            Loop Until r Is Nothing Or firstaddress = r.Address
        End If
        Application.ScreenUpdating = True
    End Sub

  7. #7
    This works great!

    Thank you very much, Bob!

    ...Cheers!

Posting Permissions

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