Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Find function does not work for merged cells. Potential BUG
As per this thread
http://www.microsoft.com/office/comm...a-1ed27880f1b5 set r = .Find(" - Open Positions ( August 03, 2007 )") returns nothing even though there is cell with that value The only peculiarity of that cell is that is merged with others Is the find function not supposed to work for merged cells? Thanks, Antonio Code and sheet follows: Sub main() Dim r As Range Dim match_address As String Dim st As String st = Worksheets(1).Range("A3").Value With Worksheets(1).Columns("A") Set r = .Find(st) If Not r Is Nothing Then match_address = r.Address End If End With End Sub ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Find function does not work for merged cells. Potential BUG
Sub Macro1()
Set rng = Selection.Find( _ What:=" - Open Positions ( August 03, 2007 )", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) rng.Select End Sub worked fine for me when I had that string entered in cells that were merged. The argument settings can make a difference. Best to specify what you want. Note that the string you are searching for does lead of with a space character, so make sure you have entered the string to match something you have in the sheet. You might test it with something less involved to make sure. -- Regards, Tom Ogilvy "Antonio" wrote: As per this thread http://www.microsoft.com/office/comm...a-1ed27880f1b5 set r = .Find(" - Open Positions ( August 03, 2007 )") returns nothing even though there is cell with that value The only peculiarity of that cell is that is merged with others Is the find function not supposed to work for merged cells? Thanks, Antonio Code and sheet follows: Sub main() Dim r As Range Dim match_address As String Dim st As String st = Worksheets(1).Range("A3").Value With Worksheets(1).Columns("A") Set r = .Find(st) If Not r Is Nothing Then match_address = r.Address End If End With End Sub ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Find function does not work for merged cells. Potential BU
It does not work even specifying all parameters
The following code Sub main() Dim r As Range Dim match_address As String With Worksheets(1).Columns("A") Set r = .Find("abc") If Not r Is Nothing Then match_address = r.Address End If End With End Sub does not find "abc" if "abc" in in column A in a merged cell, merged with the next cell to the right "Tom Ogilvy" wrote: Sub Macro1() Set rng = Selection.Find( _ What:=" - Open Positions ( August 03, 2007 )", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) rng.Select End Sub worked fine for me when I had that string entered in cells that were merged. The argument settings can make a difference. Best to specify what you want. Note that the string you are searching for does lead of with a space character, so make sure you have entered the string to match something you have in the sheet. You might test it with something less involved to make sure. -- Regards, Tom Ogilvy "Antonio" wrote: As per this thread http://www.microsoft.com/office/comm...a-1ed27880f1b5 set r = .Find(" - Open Positions ( August 03, 2007 )") returns nothing even though there is cell with that value The only peculiarity of that cell is that is merged with others Is the find function not supposed to work for merged cells? Thanks, Antonio Code and sheet follows: Sub main() Dim r As Range Dim match_address As String Dim st As String st = Worksheets(1).Range("A3").Value With Worksheets(1).Columns("A") Set r = .Find(st) If Not r Is Nothing Then match_address = r.Address End If End With End Sub ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Find function does not work for merged cells. Potential BU
I think the trick is that you have to include at least the entire merged area
in your search area. For example, in the cell containing the "abc", it was a merge area of 3 columns and two rows (A16:C17). When I expanded your Columns("A") to include columns A:C, it worked OK. Sub main() Dim r As Range Dim match_address As String With Worksheets(1).Columns("A:C") Set r = .Find(What:="ABC", _ After:=Worksheets(1).Range("A65536"), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) r.Select If Not r Is Nothing Then match_address = r.Address Debug.Print "Found at: " & match_address End If End With End Sub -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Antonio" wrote: It does not work even specifying all parameters The following code Sub main() Dim r As Range Dim match_address As String With Worksheets(1).Columns("A") Set r = .Find("abc") If Not r Is Nothing Then match_address = r.Address End If End With End Sub does not find "abc" if "abc" in in column A in a merged cell, merged with the next cell to the right "Tom Ogilvy" wrote: Sub Macro1() Set rng = Selection.Find( _ What:=" - Open Positions ( August 03, 2007 )", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) rng.Select End Sub worked fine for me when I had that string entered in cells that were merged. The argument settings can make a difference. Best to specify what you want. Note that the string you are searching for does lead of with a space character, so make sure you have entered the string to match something you have in the sheet. You might test it with something less involved to make sure. -- Regards, Tom Ogilvy "Antonio" wrote: As per this thread http://www.microsoft.com/office/comm...a-1ed27880f1b5 set r = .Find(" - Open Positions ( August 03, 2007 )") returns nothing even though there is cell with that value The only peculiarity of that cell is that is merged with others Is the find function not supposed to work for merged cells? Thanks, Antonio Code and sheet follows: Sub main() Dim r As Range Dim match_address As String Dim st As String st = Worksheets(1).Range("A3").Value With Worksheets(1).Columns("A") Set r = .Find(st) If Not r Is Nothing Then match_address = r.Address End If End With End Sub ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Find function does not work for merged cells. Potential BU
That is not an option when you do not know in advance the merge structure
"Tom Ogilvy" wrote: I think the trick is that you have to include at least the entire merged area in your search area. For example, in the cell containing the "abc", it was a merge area of 3 columns and two rows (A16:C17). When I expanded your Columns("A") to include columns A:C, it worked OK. Sub main() Dim r As Range Dim match_address As String With Worksheets(1).Columns("A:C") Set r = .Find(What:="ABC", _ After:=Worksheets(1).Range("A65536"), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) r.Select If Not r Is Nothing Then match_address = r.Address Debug.Print "Found at: " & match_address End If End With End Sub -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Antonio" wrote: It does not work even specifying all parameters The following code Sub main() Dim r As Range Dim match_address As String With Worksheets(1).Columns("A") Set r = .Find("abc") If Not r Is Nothing Then match_address = r.Address End If End With End Sub does not find "abc" if "abc" in in column A in a merged cell, merged with the next cell to the right "Tom Ogilvy" wrote: Sub Macro1() Set rng = Selection.Find( _ What:=" - Open Positions ( August 03, 2007 )", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) rng.Select End Sub worked fine for me when I had that string entered in cells that were merged. The argument settings can make a difference. Best to specify what you want. Note that the string you are searching for does lead of with a space character, so make sure you have entered the string to match something you have in the sheet. You might test it with something less involved to make sure. -- Regards, Tom Ogilvy "Antonio" wrote: As per this thread http://www.microsoft.com/office/comm...a-1ed27880f1b5 set r = .Find(" - Open Positions ( August 03, 2007 )") returns nothing even though there is cell with that value The only peculiarity of that cell is that is merged with others Is the find function not supposed to work for merged cells? Thanks, Antonio Code and sheet follows: Sub main() Dim r As Range Dim match_address As String Dim st As String st = Worksheets(1).Range("A3").Value With Worksheets(1).Columns("A") Set r = .Find(st) If Not r Is Nothing Then match_address = r.Address End If End With End Sub ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Find function does not work for merged cells. Potential BU
Didn't the workaround I suggested in your other post work for you, doesn't
require knowing in advance the merge structure, if any. Regards, Peter T "Antonio" wrote in message ... That is not an option when you do not know in advance the merge structure snip |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Find function does not work for merged cells. Potential BU
Hello Peter,
xl2003: I didn't find it necessary to select the sheet. Just using CELLS was sufficient although overkill. My suggestion had to do with the fact that he wanted to only look in column A. Using either cells or adding additional columns, the OP could just check if the found value was in column A and search again if it isn't. Sample code for that is in the FINDNEXT VBA help. -- Regards, Tom Ogilvy "Peter T" wrote: Didn't the workaround I suggested in your other post work for you, doesn't require knowing in advance the merge structure, if any. Regards, Peter T "Antonio" wrote in message ... That is not an option when you do not know in advance the merge structure snip |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Find function does not work for merged cells. Potential BU
Hi Tom,
When I tested yesterday (eventually) I found a bug in the following scenario - the find string is in merged cells - the merged area is selected - only one instance of the find string in the find range My workaround was, if at first Find didn't find, select a different cell, I used Find(""), and try again. I just tried your code, it worked perfectly. I amended to .Cells, ie whole sheet, and that also worked fine. What seems to make it work is this argument - After:=Worksheets(1).Range("A65536") This is much cleaner than my clunky idea! Antonio, I know the purpose of this thread is to point out the bug to MS. In the meantime I would suggest exploring Tom's suggestion further, or even mine. Should work without needing to "know in advance the merge structure" Regards, Peter T "Tom Ogilvy" wrote in message ... Hello Peter, xl2003: I didn't find it necessary to select the sheet. Just using CELLS was sufficient although overkill. My suggestion had to do with the fact that he wanted to only look in column A. Using either cells or adding additional columns, the OP could just check if the found value was in column A and search again if it isn't. Sample code for that is in the FINDNEXT VBA help. -- Regards, Tom Ogilvy "Peter T" wrote: Didn't the workaround I suggested in your other post work for you, doesn't require knowing in advance the merge structure, if any. Regards, Peter T "Antonio" wrote in message ... That is not an option when you do not know in advance the merge structure snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Potential Bug in Find All application | Excel Discussion (Misc queries) | |||
Script doesn't work when cells are merged. | Excel Discussion (Misc queries) | |||
Clear Contents won't work on merged cells | Excel Programming | |||
Autofit doesn't work with merged cells | Excel Discussion (Misc queries) | |||
Code doesn't work on merged cells | Excel Programming |