I Tried it, still problem...

  • Thread starter Thread starter James Burke
  • Start date Start date
J

James Burke

Thanks, but it found the wrong cell.

More Specifically, I want the macro to work as follows:
1. Go to a specific Cell
2. Copy to the clipboard all the text in that cell.
3. Paste what it copied into the Find box.
4. Go to a specific worksheet.
5. Go to the first cell (Control+home).
6. Paste what's on the clipboard into the Find Box and
Find.
7. Select the contents of that cell, and Copy it to the
clipboard.
8. Go to a specific worksheet and a specific cell, and
paste the contents of the clipboard in it.
8. Go to a specific worksheet and cell, and copy the
clipboard contents into it.

FOLLOWING IS THE CODE AS IT WAS RECORDED, WITH MY COMMENTS
IN IT TO POINT OUT WHAT'S WRONG WITH IT. (If you could
edit it to comply with my comments, that would be great.
Thanks.)
___________________________________________________________
___________
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 IN THERE 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
 
James

Reading your list and the code you recorded, here's what I put together.
There are still some parts of it that I don't understand, but maybe we can
start with this and see what needs to be modified.

Sub FindStuff()

Dim FndRng As Range

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

'If value is not found, FndRng will be Nothing
If Not FndRng Is Nothing Then
Sheets("FINDER").Range("g34").Value = FndRng.Value
Else
MsgBox "Value not found"
End If

End Sub
 
Thanks Dick. It works! You're a great guy!

You solved my biggest problem. Can the macro be amended to continue
searching the database worksheet for more possible matches, and send
the results to "FINDER" worksheet, F40, F41, F42, F43 etc?
 
JB

Try this modification

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, _
MatchCase:=False)

'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
Do

'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, _
MatchCase:=False)

'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
 
Thanks again Dick. Again, It works! You're a genius, and a really nice
person. You said in your first post, that you didn't understand some
things I did. Understand, that I'm developing a unique and marketable
product (with a programmer that I conscripted), and am therefore
reluctant to divulge exactly what I'm doing in an open forum. A person
as nice as you, however, I don't fear. If your interested in knowing
what I'm doing, e-mail me, and I'll email you back what I'm doing.
(e-mail address removed). Who knows, you may even be interested in working
on the project for potential income.

In any case, because your obviously a nice person, I'm hesitant to ask
for any more help, so if you don't have time, just ignore the following
request:

Is it possible to modify that macro to change the first three lines
(Dim FndRng As Range, Dim FirstAdd As String, Dim i As Long) to make
the macro search each word in that cell, one at a time, and send the
results to F40, F41, F42, F43 etc?

Jim:cool:
 
JB
Is it possible to modify that macro to change the first three lines
(Dim FndRng As Range, Dim FirstAdd As String, Dim i As Long) to make
the macro search each word in that cell, one at a time, and send the
results to F40, F41, F42, F43 etc?
Click to expand...

Each word of which cell?

If G9 is the cell that contains the text for which you are searching, do
want to search sheet SCIT for each word in G9? If so, try this

Sub FindStuff()

Dim FndRng As Range
Dim FirstAdd As String
Dim i As Long, j As Long
Dim FWhat As Variant

'You need XL2000 or newer to use the Split function
FWhat = Split(ActiveSheet.Range("g9").Value, " ")

'Loop through the words in G9
For j = LBound(FWhat) To UBound(FWhat)

Set FndRng = Sheets("SCIT").Cells.Find( _
what:=FWhat(j), _
after:=Sheets("SCIT").Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

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

'Store the first address found
FirstAdd = FndRng.Address

'Start the loop
Do

'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:=FWhat(j), _
after:=FndRng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

'increment the offset
i = i + 1

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

End If
Next j

End Sub
 
Thanks again Dick. I'm an artist/designer, so if you need a logo, or
some design for your website, I would be glad to make something for you
(free of charge, of course). Just let me know if you want something; I
really want to give back.

You were correct, that cell G9 is the cell I want it to search the
contents of. Cell G9 is the user input box.

About the macro, again, it works, your great, but I think I was a
little too sketchy on what I wanted. This macro does, indeed, return
the results of every word, but it also returns results that are not
desired, and that's my fault. What I need is for the macro to search
each word individually, including the space after it. That way if it
searched a word, say, "can ", it would not return "cannot".

Also, I tried to make this as simple for you as possible, figuring I
could edit it to suit my needs, but I think I made it too simple,
because the syntax is way over my head. So, this time, I'll make one
final request, be precise, and cross my fingers that I'm not asking to
much. If I am, just ignore, or tell me that this is too much to ask for
free. Just tell me how much it would cost, and I'll discuss with my
wife if we can afford it.

MACRO SPECIFICATIONS:
*Go to Cell G9 on the FINDER page.
*Go to the end of the string and add a space (" ").
*Replace all double spaces with single spaces in G9 (Find:" ",
Replace:" ")
*Again, *Replace all double spaces with single spaces in G9 (Find:" ",
Replace:" ")
*Still in G9, delete all commas (Find:"," Replace" ".)
(The above four lines will ensure that these things are not included in
the search if the user happens to type commas or double spaces)
*Search first word (including the space after it), and find all matches
in the SCIT page.
*Return the full contents of the cells that contains a match to "TEMP"
page cells A1, A2, A3, etc.
*Go back to Cell G9 on the FINDER page.
*Search the second word (including the space after it), and find all
matches in the TEMP page "A" column.
*Return the full contents of the cells that contains a match to "TEMP"
page cells B1, B2, B3, etc.
*Again, Go back to Cell G9 on the FINDER page.
*Search third word (including the space after it), and find all matches
in the TEMP page "B" column.
*Return the full contents of the cells that contains a match to "TEMP"
page cells C1, C2, C3, etc.
*Repeat this process until all words(including the space after it, even
for the last word) in G9 are searched for.
*Finally, Search the last column in the "TEMP" page and find an exact
match.
*Return contents of that cell to the next column.

You may have noticed that this macro will find inverted results via a
process of elimination.
This macro is necessary to complete my database, so that the programmer
I consigned can complete his task. Unfortunately, he's not an expert on
Excel, so he couldn't help me with this part. Again, If there is
anything I can do for you, please do not hesitate to ask. Thanks for
all your help.

Jim:)
 
Jim


each word individually, including the space after it. That way if it
searched a word, say, "can ", it would not return "cannot".
Click to expand...

However, not with a space at the end will still return "cannot", so I put a
space before and after each word in G9. It's in the What argument of the
Find method, so you can change that if it's not right.

*Finally, Search the last column in the "TEMP" page and find an exact
match.
*Return contents of that cell to the next column.
Click to expand...

I think I got everything until here. Find an exact match to what? The last
column on TEMP will be a list of matches for the last word in G9. So what
do you want to Find in that column?

Here's the revised macro

Sub FindStuff()

Dim FndRng As Range
Dim FirstAdd As String
Dim i As Long, j As Long
Dim FWhat As Variant

With Sheets("FINDER").Range("g9")
Do Until .Value = Replace(.Value, " ", " ")
.Value = Replace(.Value, " ", " ")
Loop
.Value = Replace(.Value, ",", "")

'You need XL2000 or newer to use the Split function
FWhat = Split(.Value, " ")
End With

'Loop through the words in G9
For j = LBound(FWhat) To UBound(FWhat)

Set FndRng = Sheets("SCIT").Cells.Find( _
what:=Chr(32) & FWhat(j) & Chr(32), _
after:=Sheets("SCIT").Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

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

'Store the first address found
FirstAdd = FndRng.Address

'Start the loop
Do

'Write to the sheet
Sheets("TEMP").Range("a1").Offset(i, j).Value = _
FndRng.Value

'Find the next occurrence
Set FndRng = Sheets("SCIT").Cells.Find( _
what:=Chr(32) & FWhat(j) & Chr(32), _
after:=FndRng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

'increment the offset
i = i + 1

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

End If
i = 0
Next j

End Sub
 
Thanks again Dick. At the risk of sounding repetitious, I hope that I
can do something for you in return.
I think I got everything until here. Find an exact match to what? The
Click to expand...
last column on TEMP will be a list of matches for the last word in G9.
So what do you want to Find in that column?<

This macro didn't work quite right, and I think it's because of this:

The First word it searches it should search the "SCIT"page. For every
subsuquent words it searches, it should search the last column of the
"TEMP"page (where the results are sent to). When it has searched all
words in the query, then it searches the last column of the "TEMP"page
for exact an match (nothing more, nothing less). This will produce the
inverted match, through a process of elimination.

Using the example of a query "S5 Q3 R9 "

1.)Search "SCIT"page for "S5 "
(FIND ANYTHING WITH AN "S5" IN IT, it should not find "S65 ", OR "5S
")
Returns to "TEMP"page/ColumnA:
"Q3 R9 S5 "
"Q3 R9 S5 T "
"S5 J K "
"L M S5 "
"L R9 S5 "
"Q3 S5 K "
"R9 S5 "

*********NOTE: For now on macro searches the "TEMP"page...
2.) Search "TEMP"page/ColumnA for "Q3 "
(FIND ANYTHING WITH AN "Q3" IN IT, it should not find "Q93 ", OR "Q
")
Returns to "TEMP"page/ColumnB:
"Q3 R9 S5 "
"Q3 R9 S5 T "
"Q3 S5 K "

3.) Search "TEMP"page/ColumnB for "R9 "
(FIND ANYTHING WITH AN "R9" IN IT, it should not find "R39 ", OR "R
")
Returns to "TEMP"page/ColumnC:
"Q3 R9 S5 "
"Q3 R9 S5 T "

4.) Search "TEMP"page/ColumnC an EXACT MATCH "S5 Q3 R9 "
(FIND "S5 Q3 R9 " (nothing less, nothing more)
Return to "TEMP"page/ColumnD:
"Q3 R9 S5 "
(If no exact match is found, Enter into cell of "TEMP"page/ColumnD: "No
Exact Matches...See Related Matches")

Bingo.
The query was "S5 Q3 R9 "
The inverted/exact match is "Q3 R9 S5 " (an inversion of the query).


One other thing...
You said:
However, not with a space at the end will still return "cannot", so I
Click to expand...
put a
space before and after each word in G9. It's in the What argument of
the
Find method, so you can change that if it's not right.<

For my purposes, it would be better to not search for a space before
each word in the query. I'm not sure how to change the What argument. A
search for "not " that returns "cannot " is ok.

Thanks so much.

Jim
(e-mail address removed)
 
Dick,

Oops, step 4.) of my last post is flawed. It should, intead of
searching for an exact match, search for a result that contains the
same amount of words as the query. That would produce the
exact/inverted match. If this is not possible, or it is difficult to
code, the macro would still be very useful to me (if you ended the
macro at step 3; skipping step 4).

Jim
 
Jim

OK, I think I got. Test this out and let me know if you run into problems.

Sub FindStuff()

Dim FndRng As Range
Dim FirstAdd As String
Dim i As Long, j As Long
Dim FWhat As Variant
Dim SrchRng As Range
Dim Cell As Range
Dim LastCol As Long
Dim ExactMatch As Boolean

Sheets("TEMP").Cells.ClearContents

With Sheets("FINDER").Range("g9")
Do Until .Value = Replace(.Value, " ", " ")
.Value = Replace(.Value, " ", " ")
Loop
.Value = Replace(.Value, ",", "")

'You need XL2000 or newer to use the Split function
FWhat = Split(.Value, " ")
End With

'Loop through the words in G9
For j = LBound(FWhat) To UBound(FWhat)

If j = LBound(FWhat) Then
Set SrchRng = Sheets("SCIT").Cells
Else
Set SrchRng = Sheets("TEMP").Columns(j).Cells
End If

Set FndRng = SrchRng.Find( _
what:=FWhat(j) & Chr(32), _
after:=SrchRng.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

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

'Store the first address found
FirstAdd = FndRng.Address

'Start the loop
Do

'Write to the sheet
Sheets("TEMP").Range("a1").Offset(i, j).Value = _
FndRng.Value

'Find the next occurrence
Set FndRng = SrchRng.Find( _
what:=FWhat(j) & Chr(32), _
after:=FndRng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

'increment the offset
i = i + 1

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

End If
i = 0
Next j

LastCol = UBound(FWhat) - LBound(FWhat) + 1

With Sheets("TEMP")
For Each Cell In .Range(.Cells(1, UBound(FWhat) + 1), _
.Cells(65536, UBound(FWhat) + 1).End(xlUp))

If Len(Cell.Value) - Len(Replace(Cell.Value, " ", "")) = _
LastCol Then

.Cells(i + 1, LastCol + 1).Value = Cell.Value
ExactMatch = True
i = i + 1
End If
Next Cell

If Not ExactMatch Then
.Cells(i + 1, LastCol + 1).Value = "No exact match"
End If
End With

End Sub
 
Dick,

Thank You, thank you. There is one bug, however:

If my query is "S5 Q3 R9 "
The macro you sent will also display, as an exact/inverted match,
results : "S5KK Q3 R9", which is not desired.
"S5[+space]" is what I want it to search for, not "S5". That way it
wouldn't return S5KK because there is not a space after 5. In my
database (the "SCIT" page) there is a space after every word in every
cell, even the last word in every cell, because I anticipated that the
macro would have to search for [word]+[space].

For my purposes, a return of KKS5 would be ok because in my case it
would probably never happen.

Ideally...
"S5 Q3 R9" query,should return:
S5{nothing less, nothing more]
+
Q3{nothing less, nothing more]
+
R9{nothing less, nothing more]

Jim
 
Jim

If my query is "S5 Q3 R9 "
The macro you sent will also display, as an exact/inverted match,
results : "S5KK Q3 R9", which is not desired.
Click to expand...

That doesn't get returned when I run it. Did you change the macro that I
posted?
"S5[+space]" is what I want it to search for, not "S5". That way it
wouldn't return S5KK because there is not a space after 5. In my
database (the "SCIT" page) there is a space after every word in every
cell, even the last word in every cell, because I anticipated that the
macro would have to search for [word]+[space].
Click to expand...

The two lines that look like this

what:=FWhat(j) & Chr(32), _

are what add the space and it seems to work properly for me. I can send you
the workbook I used for testing if you want to look it over and see if there
is an assumption that I'm missing.
 
Back
Top