Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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 -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "James Burke" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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 -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "-JB-" wrote in message ... 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? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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. . 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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? 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 -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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:) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
Jim
each word individually, including the space after it. That way if it searched a word, say, "can ", it would not return "cannot". 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. 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 -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
Thanks again Dick. At the risk of sounding repetitious, I hope that I
can do something for you in return. You wrote: 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?< 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 wrote: 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.< 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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 Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "-JB-" wrote in message ... 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
I Tried it, still problem...
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. 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]. 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. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |