CODEWITHSUNDEEP
vbaexcel
Elixir
Elixir

Reputation: 303

VBA Copy Paste formula search

Code below taken from a previous question asked

My Question - How do i get this to work if the find and search values are formulas? ..I've tried changing the .Value2 but doesn't seem to work.

VBA Copy Paste string search

With Sheets("SheetName") ' Change to your actual sheet name
Dim r As Range: Set r = .Range("C10:G10").Find(.Range("A10").Value2, , , xlWhole)
If Not r Is Nothing Then r.Offset(4, 0).Resize(5).Value2 = .Range("A14:A18").Value2

End With

enter image description here

Upvotes: 0

Views: 307

Answers (1)

Comintern
Comintern

Reputation: 22185

If you're looking for the results of formulas, you need to specify xlValues for the LookIn parameter. When it's blank it defaults to xlFormulas. For example, to find any formula that results in "Bar" (i.e. =CONCATENATE("B","a","r")) on Sheet "foo", you would do this:

With ActiveWorkbook.Sheets("Foo")
    Dim r As Range
    Set r = .UsedRange.Find("Bar", , xlValues, xlWhole)
    If Not r Is Nothing Then
        Debug.Print r.Address
    End If
End With

If you want to find a sheet that contains the actual formula you can either leave out the LookIn parameter entirely or explicitly specify it:

With ActiveWorkbook.Sheets("Foo")
    Dim r As Range
    Set r = .UsedRange.Find("=CONCATENATE(""B"",""a"",""r"")", , _
                            xlFormulas, xlWhole)
    If Not r Is Nothing Then
        Debug.Print r.Address
    End If
End With

Upvotes: 2

Related Questions