Copy/Paste Problem in Excel 2000

  • MS Excel 2000
    I am having trouble recording a macro that will copy the text in a cell, and paste it into the Find box, and then find the text (on a seperate worksheet). The macro Finds the text that is in the cell when I recorded it, but I want it to find was is there at the time that I run the macro.


  • You'll probably find that the macro recorder ain't that good at this sort of thing.

    Try this code - amend it to suit your specific search area.

    <font face=Courier New&gt;<SPAN style="color:#00007F"&gt;Sub</SPAN&gt; GetInput()<br&gt;<SPAN style="color:#007F00"&gt;'WillR.... OZGRID</SPAN&gt;<br&gt;&nbsp;&nbsp; <SPAN style="color:#00007F"&gt;Dim</SPAN&gt; str <SPAN style="color:#00007F"&gt;As</SPAN&gt; <SPAN style="color:#00007F"&gt;String</SPAN&gt;<br&gt;&nbsp;&nbsp; str = InputBox("Enter something")<br&gt;&nbsp;&nbsp; <SPAN style="color:#00007F"&gt;With</SPAN&gt; Worksheets("Sheet5").Range("a1:x1000") <SPAN style="color:#007F00"&gt;'or your range to search</SPAN&gt;<br&gt;&nbsp;&nbsp;&nbsp;&nbsp; .Find(What:=str, After:=ActiveCell, _<br&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LookIn:=xlValues, _<br&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _<br&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;xlNext, MatchCase:=False).Activate<br&gt;&nbsp;&nbsp; <SPAN style="color:#00007F"&gt;End</SPAN&gt; <SPAN style="color:#00007F"&gt;With</SPAN&gt;<br&gt;<SPAN style="color:#00007F"&gt;End</SPAN&gt; <SPAN style="color:#00007F"&gt;Sub</SPAN&gt;</FONT&gt;

    Hope this helps,


  • I've added some error trapping in case the value you enter can't be found

    <font face=Courier New&gt;<SPAN style="color:#00007F"&gt;Sub</SPAN&gt; GetInput2()<br&gt;<SPAN style="color:#007F00"&gt;'WillR.... OZGRID</SPAN&gt;<br&gt;<SPAN style="color:#00007F"&gt;Dim</SPAN&gt; str <SPAN style="color:#00007F"&gt;As</SPAN&gt; <SPAN style="color:#00007F"&gt;String</SPAN&gt;<br&gt;<SPAN style="color:#00007F"&gt;Dim</SPAN&gt; fCell <SPAN style="color:#00007F"&gt;As</SPAN&gt; Range<br&gt;<SPAN style="color:#00007F"&gt;Dim</SPAN&gt; R <SPAN style="color:#00007F"&gt;As</SPAN&gt; Range<br&gt;&nbsp;&nbsp;&nbsp;&nbsp;<br&gt;<SPAN style="color:#00007F"&gt;Set</SPAN&gt; R = Worksheets("Sheet5").Range("A1:X100")<br&gt;str = InputBox("Enter something")<br&gt;<br&gt;<SPAN style="color:#00007F"&gt;Set</SPAN&gt; fCell = R.Find(What:=str, _<br&gt;After:=R.Cells(R.Cells.Count), _<br&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LookIn:=xlValues, LookAt:=xlPart, _<br&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SearchOrder:=xlByRows, SearchDirection:=xlNext, _<br&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MatchCase:=False)<br&gt;<br&gt;<SPAN style="color:#00007F"&gt;If</SPAN&gt; fCell <SPAN style="color:#00007F"&gt;Is</SPAN&gt; <SPAN style="color:#00007F"&gt;Nothing</SPAN&gt; <SPAN style="color:#00007F"&gt;Then</SPAN&gt;<br&gt;MsgBox "Can<SPAN style="color:#007F00"&gt;'t find your value"</SPAN&gt;<br&gt;<SPAN style="color:#00007F"&gt;Exit</SPAN&gt; <SPAN style="color:#00007F"&gt;Sub</SPAN&gt;<br&gt;<SPAN style="color:#00007F"&gt;End</SPAN&gt; <SPAN style="color:#00007F"&gt;If</SPAN&gt;<br&gt;fCell.Activate<br&gt;<SPAN style="color:#00007F"&gt;End</SPAN&gt; <SPAN style="color:#00007F"&gt;Sub</SPAN&gt;</FONT&gt;


  • Thanks Will, but 2 problems when I ran the code you sent:
    1. It couldn't find the data (it's there!)[/quote]

    Have you re-set the ranges specified in my code to work for your ranges in your workbook?


    2. I really don't want a pop-up find box; I want to use specific cells that I have in my layout for this.

    This can be done.... just lose the inputbox and set the code to refer to your cell as opposed to an inputbox value.

  • See if the following macro does what you want (it assumes the value is there to find, so you may need to add some error checking as Will did). The code has not been tested since I don't have your workbook.

  • Try

    Dim s

    instead of Dim s As Double


    There are three types of people in this world.
    Those who can count and those who can&#039;t.

  • I'm guessing the error occurred because the Find command couldn't find what it was looking for. Can you post an example workbook? That will make it easier to test and debug the code to make it work for you.

  • Thanks Derk. I do appreciate your help, but I did get it working. Someone from another forum sent me the code:

    Sub FindStuff()

    Dim FndRng As Range
    Dim FirstAdd As String
    Dim i As Long

    Set FndRng = Sheets("SCIT").Cells.Find( _
    what:=ActiveSheet.Range("g9").Value, _
    after:=Sheets("SCIT").Range("A1"), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _

    'If value is not found, FndRng will be Nothing
    If Not FndRng Is Nothing Then

    'Store the first address found
    FirstAdd = FndRng.Address
    i = 0

    'Start the loop

    'Write to the sheet
    Sheets("FINDER").Range("g34").Offset(i, 0).Value = FndRng.Value

    'Find the next occurrence
    Set FndRng = Sheets("SCIT").Cells.Find( _
    what:=ActiveSheet.Range("g9").Value, _
    after:=FndRng, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _

    'increment the offset
    i = i + 1

    'Stop looping when it cycles back to the first one
    Loop Until FndRng.Address = FirstAdd

    End If

    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!