Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course
If Cell Contains Paste Value
Hi,
I'm trying to write some code where it'll search each cell within a specified range and for each cell that contains =MIK it'll copy and paste each cell as a value. I've come up with the following but I think the .contains part isn't a valid command. Can anyone help? Thanks.
Option Explicit
Dim c as Range
Sheets("Trading Summary").Select
For Each c In Range("F36:M53")
If c.Contains = "=MIK" = True Then c.Copy c.PasteSpecial
Next c
RE: If Cell Contains Paste Value
Hi Mark
I've found a way which may help you continue.
See the code below. What it does is...
If a cell in the range F6:M53 starts with =MIK then the cell is copied and pasted as a value into the A column.
(Change A in the code to another location if you want)
Sub TestCopy()
Dim c As Range
Dim n As Integer 'counts the number of MIK cells copied
n = 0
Sheets("Trading Summary").Select
For Each c In Range("F6:M53")
If Left(c.Formula, 4) = "=MIK" Then
n = n + 1
c.Copy
Range("A" & n).PasteSpecial Paste:=xlPasteValues
End If
Next c
End Sub
Hope it helps.
Note =MIK has to be at the start of the cell. Is that ok or must it be found anywhere within the cell?
Regards
Doug
Best STL
