Excel 2003 VBA: Find Method with Variable

[DrNyet]

My problem is with the line beginning "Selection.Find."

I want it to paste the clipboard contents into the search box, but it's
remembering the specific value used when I set up the macro (i.e. copied the
keystrokes).

    Selection.Copy
    Windows("Master TEST.xls").Activate
    Columns("D:D").Select
    Selection.Find(What:="359", After:=ActiveCell, LookIn:=xlFormulas,
    LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
        MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    Range(ActiveCell, ActiveCell.Offset(0, 1)).Copy
    Windows("New.xls").Activate
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, -1).Select

Thanks for any help

[TMS]

You need to get the search value from "somewhere". This could be from an InputBox or by putting the data in a cell. The value in the cell could be from a Data Validation drop down box based on data in the spreadsheet, that is, a list of (unique) values.

Let's say the data you want to search for is in cell A1, the line would look like:

    Selection.Find(What:=Range("A1").Value, After:=ActiveCell, LookIn:=xlFormulas,

But you don't need to select the column first; you can use:

    Columns("D:D").Find(What:=Range("A1").Value, After:=ActiveCell, LookIn:=xlFormulas,

Regards

[JBeaucaire]

TMShucks is correct in that using a cell is probably better than the clipboard, you don't really need to copy it into memory first.

But this routine WILL put the current clipboard contents into a usable variable:

Dim MyData As DataObject:   Set MyData = New DataObject
Dim sClipText As String

'Remember to copy something into memory before running this

On Error Resume Next
MyData.GetFromClipboard         ' Get data from the clipboard.
If Err <> 0 Then
    MsgBox "Data on clipboard is not text."
    Exit Sub
End If

sClipText = MyData.GetText(1)   ' Assign clipboard contents to string variable.

To use this method you will need to add a reference to Microsoft Forms 2.0 Library. (VBA > Tools > References)

[romperstomper]

Based on your original code:

DIm rngFound as Range
    set rngfound = Workbooks("Master TEST.xls").Activesheet.Columns("D:D").Find(What:=Selection.Value, LookIn:=xlFormulas,
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext,
        MatchCase:=False, SearchFormat:=False)
    If not rngFound is Nothing then
       rngFound.Resize(, 2).Copy selection.offset(0, 1)

I assumed you were copying back to the current workbook.

It also appears that this is probably inside a loop, in which case all the selecting you are doing is unnecessary and slows the code down - if you post the full code, I'm sure we can streamline it.

[snb]

e.g.

on error resume next
selection.offset(, 1).resize(,2)=Workbooks("Master TEST.xls").Activesheet.Columns(4).Find(Selection.Value, ,xlFormulas,xlPart).Resize(, 2).Value

[DrNyet]

Thank you for the responses. You have been very generous.

This is the entire code other than the subroutine name and close.

The goal is to enter a item number in a "New" workbook and have it retrieve the description and color from a Master workbook, and add those to New, to be used as a list for production.

I am sadly unversed in VBA and only know enough to look for examples to incorporate. The two workbooks involved are small so speed is not much of an issue although I appreciate any improvement and the opportunity to understand more about it.