Special Copy, Paste Special macro
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). |
Special Copy, Paste Special macro
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 --------------- Jacob Skaria "usmc-r70" wrote: 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). |
Special Copy, Paste Special macro
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 "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). |
Special Copy, Paste Special macro
Your code did exactly what I needed, thanks. "Á÷À˵ÄË«Óã" wrote: 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 "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). |
All times are GMT +1. The time now is 06:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com