Special Copy, Paste Special macro

U

usmc-r70

I need a macro button that will, on selection, evaluate if a cell from
A39:A79 contains the text "Not Locked" and then will change all the
corresponding formulas in those rows for columns B:I (B39:I79) into text
(i.e. Their formula values: Copy > Paste Special > Paste Values).
 
J

Jacob Skaria

The below works on the activesheet ..Try and feedback..

Sub Macro1()
Dim varData As Variant
If WorksheetFunction.CountIf(Range("A39:A79"), "*Not Locked*") > 0 Then
varData = Range("B39:I79")
Range("B39:I79").Value = varData
End If
End Sub

If this post helps click Yes
 
Á

Á÷À˵ÄË«Óã

Try the code as following.

Dim rng As Range
For Each rng In Range("A39:A79 ")
If rng.Value = "Not Locked" Then
Range("B" & rng.Row & ":I" & rng.Row).Value = Range("B" &
rng.Row & ":I" & rng.Row).Value
End If
Next rng
 
U

usmc-r70

Your code did exactly what I needed, thanks.

Ã÷À˵ÄË«Óã said:
Try the code as following.

Dim rng As Range
For Each rng In Range("A39:A79 ")
If rng.Value = "Not Locked" Then
Range("B" & rng.Row & ":I" & rng.Row).Value = Range("B" &
rng.Row & ":I" & rng.Row).Value
End If
Next rng
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top