CODEWITHSUNDEEP
vbaexcel
jordancole
jordancole

Reputation: 11

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

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

Upvotes: 1

Views: 61

Answers (2)

user4691433
user4691433

Reputation:

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

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166156

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

Upvotes: 0

Related Questions