Find and past value

J

J. Stephen

New member
Joined
Mar 31, 2012
Messages
6
Reaction score
0
Points
0
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
 
Change Cut to Copy
 
Thanks, Bob

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


any ideas?
 
It is probably something to do with losing context, but not sure what. Can you post your workbbook?
 
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
 
This works great!

Thank you very much, Bob!

...Cheers!
 
You must log in or register to reply here.
Back
Top