CODEWITHSUNDEEP
excelvba
DW K
DW K

Reputation: 1

VBA : Paste in Find command

I want to write the following Context using VBA.

When a specific word is selected in sheet 1, I want to find a specific word in sheet 2 and move it to that cell.

Selection.Copy
Sheets("Sheet2").Select
Cell.Find(What:=Apple, After:=ActiveCell, LookIn:=xlFormulas2, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , MatchByte:=False, SearchFormat:=False).Activate

End Sub

I want to put the word(Clipboard Paste?) to be copied instead of Apple, how can I do in this case ?

Upvotes: 0

Views: 59

Answers (1)

FunThomas
FunThomas

Reputation: 29171

Don't use the Clipboard. The idea of the Clipboard is to exchange data between Applications. Just write the content of the selected cell into an intermediate variable and use that as search term.

And never access the result of a Find-command directly. If Find fails to find something, it will return Nothing, you will get nasty runtime errors when you execute Nothing.Activate. Instead, assign the result of the Find to another variable and check if it contains something:

Dim searchTerm as String
searchTerm = ActiveCell.Value
Sheets("Sheet2").Select
Dim hitRange as Range
Set hitRange = Cells.Find(What:=searchTerm, After:=ActiveCell, LookIn:=xlFormulas2, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, MatchByte:=False, SearchFormat:=False)
If Not hitRange Is Nothing Then hitRange.Activate

A remark: Usually it's a bad idea to use Select and Activate in your code. I just left it there because I assume that the reason for your Activate is to show the result to the user. If you plan to do something else with the result, don't use Activate. Instead, work with the Range variable (hitRange).

Upvotes: 1

Related Questions