simple copy/paste based on criteria

[snowman1976]

hello
I tried searching the posts for this, but everything I find is more complicated than I need.

Basically, I need some code that will look in column B for the word planned. if it finds it I want it to copy range Q:W in the same row, and paste it as special values (to wipe out a formula).

Can anyone help with this?
Ultimately, if at all possible I would like the Q:W range to be easily changed by the user, either by a cell reference or some type of input at the start of the code. This is just a nice -to-have, I would be very happy with just getting the first part done

thanks in advance

 

[njimack]

Something like this (untested)...

Sub test()
Dim Last_Row As Long
Dim r As Range

Last_Row = Range("B" & Rows.Count).End(xlUp).Row

For Each r In Range("B2:B" & Last_Row)
    If r.Value = "Planned" Then
        With Range("Q" & r.Row & ":W" & r.Row)
            .Formula = .Value
        End With
    End If
Next r


End Sub

 

[snowman1976]

this appears to work , thanks Neil