Excel VBA - Paste into Find Command

Question

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!

SCB · Accepted Answer · 2014-07-31 13:48:06Z

1

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.

answered Jul 31, 2014 at 13:48

SCB

6,1891 gold badge36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SCB Over a year ago

@Kyle No problem. If a solution works for you, consider marking it as correct by clicking the tick mark underneath the voting box. It helps other people find the answer quicker in the future, and helps out the people who answered as well. Again, happy to be able to help.

Collectives™ on Stack Overflow

Excel VBA - Paste into Find Command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related