MS EXCEL 2000:
Hi,
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 was in the cell when I recorded it, but I
want it to find what is there at the time that I run the
macro. Any help would be greatly appreciated.

More Specifically, I want the macro to work as follows:
' Go to a specific Cell
' Copy to the clipboard all the text in that cell.
' Paste what it copied into the Find box.
' Go to a specific worksheet.
' Go to A1 cell
' Paste what's on the clipboard into the Find Box and Find.
' Select the contents of that cell, and Copy it to the
clipboard.
' Go to a specific worksheet and a specific cell, and
paste the contents of the clipboard in it.

FOLLOWING IS THE CODE AS IT WAS RECORDED, WITH MY COMMENTS
IN IT TO POINT OUT WHAT'S WRONG WITH IT.
__________________________________________________ _________
___________
Sub Macro5()
'
Application.Goto Reference:="R9C7"
ActiveCell.Range("A1:T1").Select
ActiveCell.FormulaR1C1 = "1 2 b3 4 5 6 b7"
' [WRONG! COPY WHATEVER IS IN THERE AT THE TIME I RUN THE
MACRO]
Sheets("SCIT").Select
Application.Goto Reference:="R1C1"
Cells.Find(What:="1 2 b3 4 5 6 b7", After:=ActiveCell,
LookIn:=xlFormulas _
' [WRONG! FIND WHATEVER IS ON THE CLIPBOARD AT THE TIME I
RUN THE MACRO]
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.FormulaR1C1 = "1 2 b3 4 5 6 b7 "
' [WRONG! ACTIVATE WHATEVER IS FOUND THERE AT THE TIME I
RUN THE MACRO]
Sheets("FINDER").Select
Application.Goto Reference:="R34C7"
ActiveCell.Select
ActiveCell.FormulaR1C1 = _
"Major-Mode 1 - Ionian (The Major Scale) 1 2 b3 4
5 6 b7 "
' [WRONG! THE TEXT SHOULD BE WHATEVER IS IN THERE AT THE
TIME I RUN THE MACRO]
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

You don't need to do all the selecting:

Public Sub FindWhatsInActiveCell()
Dim found As Range
With Sheets("SCIT").Cells
Set found = .Find(What:=ActiveCell.Value, _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
If Not found Is Nothing Then _
found.Copy Destination:=Sheets("FINDER").Range("G34")
Application.Goto Sheets("Finder").Range("G35")
End Sub

This looks, on worksheet SCIT, for a cell with the same text as the
active cell. If it finds it (i.e. Not found Is Nothing), then it
copies the found cell's contents to FINDER!G34.

I left the Goto Finder!G35 in there since I'm not sure why you
wanted to end up there.

The With...End With syntax uses the "." as shorthand for whatever
comes after the With keyword, so

With Sheets("SCIT").Cells
Set found = .Find(What:=ActiveCell.Value, _
After:=.Cells(1, 1)
...

is equivalent to

Set found = Sheets("SCIT").Cells.Find(What:=ActiveCell.Value, _
After:=Sheets("SCIT").Cells(1, 1), _
...

In article ,
"James Burke" wrote:

MS EXCEL 2000:
Hi,
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 was in the cell when I recorded it, but I
want it to find what is there at the time that I run the
macro. Any help would be greatly appreciated.

More Specifically, I want the macro to work as follows:
' Go to a specific Cell
' Copy to the clipboard all the text in that cell.
' Paste what it copied into the Find box.
' Go to a specific worksheet.
' Go to A1 cell
' Paste what's on the clipboard into the Find Box and Find.
' Select the contents of that cell, and Copy it to the
clipboard.
' Go to a specific worksheet and a specific cell, and
paste the contents of the clipboard in it.

FOLLOWING IS THE CODE AS IT WAS RECORDED, WITH MY COMMENTS
IN IT TO POINT OUT WHAT'S WRONG WITH IT.
__________________________________________________ _________
___________
Sub Macro5()
'
Application.Goto Reference:="R9C7"
ActiveCell.Range("A1:T1").Select
ActiveCell.FormulaR1C1 = "1 2 b3 4 5 6 b7"
' [WRONG! COPY WHATEVER IS IN THERE AT THE TIME I RUN THE
MACRO]
Sheets("SCIT").Select
Application.Goto Reference:="R1C1"
Cells.Find(What:="1 2 b3 4 5 6 b7", After:=ActiveCell,
LookIn:=xlFormulas _
' [WRONG! FIND WHATEVER IS ON THE CLIPBOARD AT THE TIME I
RUN THE MACRO]
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.FormulaR1C1 = "1 2 b3 4 5 6 b7 "
' [WRONG! ACTIVATE WHATEVER IS FOUND THERE AT THE TIME I
RUN THE MACRO]
Sheets("FINDER").Select
Application.Goto Reference:="R34C7"
ActiveCell.Select
ActiveCell.FormulaR1C1 = _
"Major-Mode 1 - Ionian (The Major Scale) 1 2 b3 4
5 6 b7 "
' [WRONG! THE TEXT SHOULD BE WHATEVER IS IN THERE AT THE
TIME I RUN THE MACRO]
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Thanks J.E, but I got an error "Compile error: Expected
End Sub"

Copy from

Public Sub FindWhatsInActiveCell()

to

End Sub

and paste it into the module.


In article ,
"James Burke" wrote:

Thanks J.E, but I got an error "Compile error: Expected
End Sub"

Thanks J.E, but I got an error "Compile error: Expected End Sub"



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Copy from

Public Sub FindWhatsInActiveCell()

to

End Sub

and paste it into the module.

Public SubIn article ,
-JB- wrote:

Thanks J.E, but I got an error "Compile error: Expected End Sub"