CODEWITHSUNDEEP
vbaexcel
Kyle
Kyle

Reputation: 13

Excel VBA - Paste into Find Command

I'm trying to create a macro which searches for a string of text that has been entered/selected from another cell. When I record the macro, it sets the "What" part of the find function as the specific text that was copied when recording the macro, rather than a Paste Selection, which is what I want.

Sub GOTOSECTION() ' ' GOTOSECTION Macro '

'
Range("B7").Select

Selection.Copy
Cells.Find(What:="Section 4A", After:= _
    ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
End Sub

But I want the "What:" to be the value copied from cell B7. When I try to input a paste command in there it gives me a syntax error, so I'm sure it's something really basic I'm missing (I'm pretty green when it comes to vba, and I've been unable to find examples of what I'm looking for online). Thanks for any input!

Upvotes: 1

Views: 3451

Answers (1)

SCB
SCB

Reputation: 6129

Replace What:="Section 4A" with What:=ActiveCell.Value

This will get the current value of the currently selected cell (though it might (though I'm not 100% sure without checking) cause some errors if the cell is blank, so consider error checking).

EDIT: If you're continuing to use the macro recording in the future, it might be worth looking into relative references. I've never used it for anything like this, so I'm not sure if it will have an effect, but it could, so check it out.

Upvotes: 1

Related Questions