VBA: How do I search for a formula driven value?

Question

I am trying to search for the date, which I have in cell K1 as =today()....

Whenever I am recording the macro, however, it will continue to use the date which the macro was written. What I am curious about is a methodology of pasting what is on the clipboard into the VBA search function.

So I want to search column A for what is in cell K1. This is what code I have now (the find what is what I just typed to help you all have an idea of what I'm looking for)

Range("K1").Select
Selection.Copy
Columns("A:A").Select
Selection.Find(What:="COPY CONTENTS OF CELL K1", After:=ActiveCell,    LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

OP have you actually tried to run the code above? Simply pasting some code saying 'this is what I have now' doesnt tell us what fails in that code. Explain what you want it to do, and what it actually does. — Grade 'Eh' Bacon, Aug 12 '15 at 17:54

score 1 · Answer 1 · answered Aug 12 '15 at 17:54

1

This should do it. Note that your original code will throw an error if today's date isn't found. Typically when working with Find() it's safer to test the returned value before trying to do something with it.

Dim f As Range
Set f = Columns("A:A").Find(What:=Date(), After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not f Is Nothing then
    'do something with f
Else
    '???? do what
End If

answered Aug 12 '15 at 17:54

Tim Williams

154,628
8
97
125

Hey Tim, just saw your answer and mine is very similar. Feel free to delete mine/merge whatever extra bits I have if it's appropriate. – puzzlepiece87 Aug 12 '15 at 18:00
@puzzlepiece87 while similar, Tim's code caters for no match with the `Is Nothing` test. Its a better way to go. – brettdj Aug 13 '15 at 03:54
@brettdj I completely agree, *and* he won the fastest gun contest :P I am not completely sure of the meta on two questions entered at the same time, but I was saying that since mine doesn't have much value that Tim's answer doesn't already have, he can feel free to delete my answer and take anything extra I might have added for his answer :) – puzzlepiece87 Aug 13 '15 at 14:55
I don't care either way - I'm certainly not going to delete anyone else's answer. – Tim Williams Aug 13 '15 at 15:56

score 0 · Answer 2 · edited May 23 '17 at 11:52

You could replace your entire code with this:

Activesheet.Columns("A:A").Find(What:=ActiveSheet.Range("K1"), After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

What was happening is that there's no way to paste a cell's contents via .Copy directly into a VBA function. So I placed what you wanted to paste into it into the function directly.

I think you will also find this helpful: How to avoid using Select in Excel VBA macros

VBA: How do I search for a formula driven value?

2 Answers2