| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with find in merged cells
I thought I had this VBA pretty under control, but this one is new to me.
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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
I'd bet that .find is supposed to work with merged cells. And that it's a bug
that it fails. Another reason not to use merged cells????? Antonio wrote: I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
Hi Antonio,
That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
It failed for me when I specified all the parms.
Edit|Find even failed for me when I did this. I started a new worksheet I put asdf in A5 I selected A5:D13 I clicked the Merge and Center icon on the formatting toolbar I hit ctrl-f to see the Find dialog. I typed asdf and hit the Find Next button xl2003 came back with a couldn't find the data I'm search for message. Oddly, if I hit the Find All button, it was found. (Merged cells are a PITA.) Peter T wrote: Hi Antonio, That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
Bad news ... as much as I dislike merged cells, I followed your steps
(xl2003, WinXP) and it found it - both with find next and Find All. Find All even properly reported that it was in $A$5. But the code definitely doesn't find it when set up as you did. It will find it if the cells are not merged (and as long as the entry is really on Sheets(1) - which mine wasn't for a while). "Dave Peterson" wrote: It failed for me when I specified all the parms. Edit|Find even failed for me when I did this. I started a new worksheet I put asdf in A5 I selected A5:D13 I clicked the Merge and Center icon on the formatting toolbar I hit ctrl-f to see the Find dialog. I typed asdf and hit the Find Next button xl2003 came back with a couldn't find the data I'm search for message. Oddly, if I hit the Find All button, it was found. (Merged cells are a PITA.) Peter T wrote: Hi Antonio, That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
Why is that bad news!
But your code failed, was that while the merged cell containing the text was selected and no other similar text existed in the find range. That's the only scenario I can get to fail, both manually and with code. This workaround works for me, does it for you guys - Sub Macro1() Range("A5:D13").HorizontalAlignment = xlCenter Range("A5:D13").Merge Range("A5:D13") = "hello Summer" Range("A1").Select End Sub Sub FindTest() Dim bFindBlank As Boolean Dim rFound As Range Dim sFindWhat As String sFindWhat = "summer" bFindBlank = False reTryFind: Set rFound = ActiveSheet.Cells.Find( _ What:=sFindWhat, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If rFound Is Nothing And (Not bFindBlank) Then If ShtHasMergedCells(ActiveSheet) Then bFindBlank = True ActiveSheet.Cells.Find("").Select GoTo reTryFind End If End If If Not rFound Is Nothing Then rFound.Parent.Activate rFound.Select MsgBox rFound.MergeArea.Address(0, 0) Else MsgBox "Not found" End If End Sub Function ShtHasMergedCells(ws As Worksheet) As Boolean Dim v As Variant v = ws.Cells.MergeCells If v = False Then ShtHasMergedCells = False Else ' Null or True ShtHasMergedCells = True End If End Function Regards, Peter T "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bad news ... as much as I dislike merged cells, I followed your steps (xl2003, WinXP) and it found it - both with find next and Find All. Find All even properly reported that it was in $A$5. But the code definitely doesn't find it when set up as you did. It will find it if the cells are not merged (and as long as the entry is really on Sheets(1) - which mine wasn't for a while). "Dave Peterson" wrote: It failed for me when I specified all the parms. Edit|Find even failed for me when I did this. I started a new worksheet I put asdf in A5 I selected A5:D13 I clicked the Merge and Center icon on the formatting toolbar I hit ctrl-f to see the Find dialog. I typed asdf and hit the Find Next button xl2003 came back with a couldn't find the data I'm search for message. Oddly, if I hit the Find All button, it was found. (Merged cells are a PITA.) Peter T wrote: Hi Antonio, That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
'Bad news" because of inconsistent results under what should have been
duplicate test conditions. Your workaround code did find it and reported the merge cell address properly. Good deal. "Peter T" wrote: Why is that bad news! But your code failed, was that while the merged cell containing the text was selected and no other similar text existed in the find range. That's the only scenario I can get to fail, both manually and with code. This workaround works for me, does it for you guys - Sub Macro1() Range("A5:D13").HorizontalAlignment = xlCenter Range("A5:D13").Merge Range("A5:D13") = "hello Summer" Range("A1").Select End Sub Sub FindTest() Dim bFindBlank As Boolean Dim rFound As Range Dim sFindWhat As String sFindWhat = "summer" bFindBlank = False reTryFind: Set rFound = ActiveSheet.Cells.Find( _ What:=sFindWhat, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If rFound Is Nothing And (Not bFindBlank) Then If ShtHasMergedCells(ActiveSheet) Then bFindBlank = True ActiveSheet.Cells.Find("").Select GoTo reTryFind End If End If If Not rFound Is Nothing Then rFound.Parent.Activate rFound.Select MsgBox rFound.MergeArea.Address(0, 0) Else MsgBox "Not found" End If End Sub Function ShtHasMergedCells(ws As Worksheet) As Boolean Dim v As Variant v = ws.Cells.MergeCells If v = False Then ShtHasMergedCells = False Else ' Null or True ShtHasMergedCells = True End If End Function Regards, Peter T "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bad news ... as much as I dislike merged cells, I followed your steps (xl2003, WinXP) and it found it - both with find next and Find All. Find All even properly reported that it was in $A$5. But the code definitely doesn't find it when set up as you did. It will find it if the cells are not merged (and as long as the entry is really on Sheets(1) - which mine wasn't for a while). "Dave Peterson" wrote: It failed for me when I specified all the parms. Edit|Find even failed for me when I did this. I started a new worksheet I put asdf in A5 I selected A5:D13 I clicked the Merge and Center icon on the formatting toolbar I hit ctrl-f to see the Find dialog. I typed asdf and hit the Find Next button xl2003 came back with a couldn't find the data I'm search for message. Oddly, if I hit the Find All button, it was found. (Merged cells are a PITA.) Peter T wrote: Hi Antonio, That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
So is this a bug or not?
My workaround is just: Application.Cells.MergeCells = False "JLatham" wrote: 'Bad news" because of inconsistent results under what should have been duplicate test conditions. Your workaround code did find it and reported the merge cell address properly. Good deal. "Peter T" wrote: Why is that bad news! But your code failed, was that while the merged cell containing the text was selected and no other similar text existed in the find range. That's the only scenario I can get to fail, both manually and with code. This workaround works for me, does it for you guys - Sub Macro1() Range("A5:D13").HorizontalAlignment = xlCenter Range("A5:D13").Merge Range("A5:D13") = "hello Summer" Range("A1").Select End Sub Sub FindTest() Dim bFindBlank As Boolean Dim rFound As Range Dim sFindWhat As String sFindWhat = "summer" bFindBlank = False reTryFind: Set rFound = ActiveSheet.Cells.Find( _ What:=sFindWhat, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If rFound Is Nothing And (Not bFindBlank) Then If ShtHasMergedCells(ActiveSheet) Then bFindBlank = True ActiveSheet.Cells.Find("").Select GoTo reTryFind End If End If If Not rFound Is Nothing Then rFound.Parent.Activate rFound.Select MsgBox rFound.MergeArea.Address(0, 0) Else MsgBox "Not found" End If End Sub Function ShtHasMergedCells(ws As Worksheet) As Boolean Dim v As Variant v = ws.Cells.MergeCells If v = False Then ShtHasMergedCells = False Else ' Null or True ShtHasMergedCells = True End If End Function Regards, Peter T "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bad news ... as much as I dislike merged cells, I followed your steps (xl2003, WinXP) and it found it - both with find next and Find All. Find All even properly reported that it was in $A$5. But the code definitely doesn't find it when set up as you did. It will find it if the cells are not merged (and as long as the entry is really on Sheets(1) - which mine wasn't for a while). "Dave Peterson" wrote: It failed for me when I specified all the parms. Edit|Find even failed for me when I did this. I started a new worksheet I put asdf in A5 I selected A5:D13 I clicked the Merge and Center icon on the formatting toolbar I hit ctrl-f to see the Find dialog. I typed asdf and hit the Find Next button xl2003 came back with a couldn't find the data I'm search for message. Oddly, if I hit the Find All button, it was found. (Merged cells are a PITA.) Peter T wrote: Hi Antonio, That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
I definitively think this is a bug and that it should be reported to MS.
I am using xl2003 and xp2 The cell does contain the string I am looking for the Ctrl-F finds it but the .find VBA function does not (even setting all the parameters correctly, to make sure I just copied the same .find as one in the same code that did work because the cell was not merged) What is the process to report the bug. "Antonio" wrote: So is this a bug or not? My workaround is just: Application.Cells.MergeCells = False "JLatham" wrote: 'Bad news" because of inconsistent results under what should have been duplicate test conditions. Your workaround code did find it and reported the merge cell address properly. Good deal. "Peter T" wrote: Why is that bad news! But your code failed, was that while the merged cell containing the text was selected and no other similar text existed in the find range. That's the only scenario I can get to fail, both manually and with code. This workaround works for me, does it for you guys - Sub Macro1() Range("A5:D13").HorizontalAlignment = xlCenter Range("A5:D13").Merge Range("A5:D13") = "hello Summer" Range("A1").Select End Sub Sub FindTest() Dim bFindBlank As Boolean Dim rFound As Range Dim sFindWhat As String sFindWhat = "summer" bFindBlank = False reTryFind: Set rFound = ActiveSheet.Cells.Find( _ What:=sFindWhat, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If rFound Is Nothing And (Not bFindBlank) Then If ShtHasMergedCells(ActiveSheet) Then bFindBlank = True ActiveSheet.Cells.Find("").Select GoTo reTryFind End If End If If Not rFound Is Nothing Then rFound.Parent.Activate rFound.Select MsgBox rFound.MergeArea.Address(0, 0) Else MsgBox "Not found" End If End Sub Function ShtHasMergedCells(ws As Worksheet) As Boolean Dim v As Variant v = ws.Cells.MergeCells If v = False Then ShtHasMergedCells = False Else ' Null or True ShtHasMergedCells = True End If End Function Regards, Peter T "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bad news ... as much as I dislike merged cells, I followed your steps (xl2003, WinXP) and it found it - both with find next and Find All. Find All even properly reported that it was in $A$5. But the code definitely doesn't find it when set up as you did. It will find it if the cells are not merged (and as long as the entry is really on Sheets(1) - which mine wasn't for a while). "Dave Peterson" wrote: It failed for me when I specified all the parms. Edit|Find even failed for me when I did this. I started a new worksheet I put asdf in A5 I selected A5:D13 I clicked the Merge and Center icon on the formatting toolbar I hit ctrl-f to see the Find dialog. I typed asdf and hit the Find Next button xl2003 came back with a couldn't find the data I'm search for message. Oddly, if I hit the Find All button, it was found. (Merged cells are a PITA.) Peter T wrote: Hi Antonio, That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
If you're coming in through Microsoft discussion forum of Excel, just start a
new thread and choose the "Suggestion for Microsoft" type of thread - that will flag it for their attention. Describe the problem in detail and perhaps even provide a link to the beginning of this discussion for reference. If you're not coming in directly, you can get there through this link: http://www.microsoft.com/office/comm...verview.mspx#2 Just choose Excel, and probably the Application Errors group would be the most logical place to post it: http://www.microsoft.com/office/comm...&lang=en&cr=US "Antonio" wrote: I definitively think this is a bug and that it should be reported to MS. I am using xl2003 and xp2 The cell does contain the string I am looking for the Ctrl-F finds it but the .find VBA function does not (even setting all the parameters correctly, to make sure I just copied the same .find as one in the same code that did work because the cell was not merged) What is the process to report the bug. "Antonio" wrote: So is this a bug or not? My workaround is just: Application.Cells.MergeCells = False "JLatham" wrote: 'Bad news" because of inconsistent results under what should have been duplicate test conditions. Your workaround code did find it and reported the merge cell address properly. Good deal. "Peter T" wrote: Why is that bad news! But your code failed, was that while the merged cell containing the text was selected and no other similar text existed in the find range. That's the only scenario I can get to fail, both manually and with code. This workaround works for me, does it for you guys - Sub Macro1() Range("A5:D13").HorizontalAlignment = xlCenter Range("A5:D13").Merge Range("A5:D13") = "hello Summer" Range("A1").Select End Sub Sub FindTest() Dim bFindBlank As Boolean Dim rFound As Range Dim sFindWhat As String sFindWhat = "summer" bFindBlank = False reTryFind: Set rFound = ActiveSheet.Cells.Find( _ What:=sFindWhat, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If rFound Is Nothing And (Not bFindBlank) Then If ShtHasMergedCells(ActiveSheet) Then bFindBlank = True ActiveSheet.Cells.Find("").Select GoTo reTryFind End If End If If Not rFound Is Nothing Then rFound.Parent.Activate rFound.Select MsgBox rFound.MergeArea.Address(0, 0) Else MsgBox "Not found" End If End Sub Function ShtHasMergedCells(ws As Worksheet) As Boolean Dim v As Variant v = ws.Cells.MergeCells If v = False Then ShtHasMergedCells = False Else ' Null or True ShtHasMergedCells = True End If End Function Regards, Peter T "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bad news ... as much as I dislike merged cells, I followed your steps (xl2003, WinXP) and it found it - both with find next and Find All. Find All even properly reported that it was in $A$5. But the code definitely doesn't find it when set up as you did. It will find it if the cells are not merged (and as long as the entry is really on Sheets(1) - which mine wasn't for a while). "Dave Peterson" wrote: It failed for me when I specified all the parms. Edit|Find even failed for me when I did this. I started a new worksheet I put asdf in A5 I selected A5:D13 I clicked the Merge and Center icon on the formatting toolbar I hit ctrl-f to see the Find dialog. I typed asdf and hit the Find Next button xl2003 came back with a couldn't find the data I'm search for message. Oddly, if I hit the Find All button, it was found. (Merged cells are a PITA.) Peter T wrote: Hi Antonio, That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem with find in merged cells
done, thanks.
"Antonio" wrote: So is this a bug or not? My workaround is just: Application.Cells.MergeCells = False "JLatham" wrote: 'Bad news" because of inconsistent results under what should have been duplicate test conditions. Your workaround code did find it and reported the merge cell address properly. Good deal. "Peter T" wrote: Why is that bad news! But your code failed, was that while the merged cell containing the text was selected and no other similar text existed in the find range. That's the only scenario I can get to fail, both manually and with code. This workaround works for me, does it for you guys - Sub Macro1() Range("A5:D13").HorizontalAlignment = xlCenter Range("A5:D13").Merge Range("A5:D13") = "hello Summer" Range("A1").Select End Sub Sub FindTest() Dim bFindBlank As Boolean Dim rFound As Range Dim sFindWhat As String sFindWhat = "summer" bFindBlank = False reTryFind: Set rFound = ActiveSheet.Cells.Find( _ What:=sFindWhat, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If rFound Is Nothing And (Not bFindBlank) Then If ShtHasMergedCells(ActiveSheet) Then bFindBlank = True ActiveSheet.Cells.Find("").Select GoTo reTryFind End If End If If Not rFound Is Nothing Then rFound.Parent.Activate rFound.Select MsgBox rFound.MergeArea.Address(0, 0) Else MsgBox "Not found" End If End Sub Function ShtHasMergedCells(ws As Worksheet) As Boolean Dim v As Variant v = ws.Cells.MergeCells If v = False Then ShtHasMergedCells = False Else ' Null or True ShtHasMergedCells = True End If End Function Regards, Peter T "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bad news ... as much as I dislike merged cells, I followed your steps (xl2003, WinXP) and it found it - both with find next and Find All. Find All even properly reported that it was in $A$5. But the code definitely doesn't find it when set up as you did. It will find it if the cells are not merged (and as long as the entry is really on Sheets(1) - which mine wasn't for a while). "Dave Peterson" wrote: It failed for me when I specified all the parms. Edit|Find even failed for me when I did this. I started a new worksheet I put asdf in A5 I selected A5:D13 I clicked the Merge and Center icon on the formatting toolbar I hit ctrl-f to see the Find dialog. I typed asdf and hit the Find Next button xl2003 came back with a couldn't find the data I'm search for message. Oddly, if I hit the Find All button, it was found. (Merged cells are a PITA.) Peter T wrote: Hi Antonio, That works fine for me, ie your sample text in merged cells. It might not work if: - Previously set Find options are not what you require, eg Match case and/or Entire Cells are ticked. Set options as required in code. - The text does not exist in whatever range you qualified .Find with. Try Activesheet.Cells Regards, Peter T "Antonio" wrote in message ... I thought I had this VBA pretty under control, but this one is new to me. 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 Sheet: Average Credit Balance 51,608.69 51,608.69 0 - Open Positions ( August 03, 2007 ) Stocks Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized P/L EUR CS -- 7,000 1 -- -- 28.25 197,750.00 -- EAD -- 8,000 1 -- -- 21.92 175,360.00 -- FPB -- 5,000 1 -- -- 54.71 273,550.00 -- -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find range of merged cells | Excel Programming | |||
| Find Merged Cells | Excel Discussion (Misc queries) | |||
| how can I find merged cells in a large xl-file | Excel Discussion (Misc queries) | |||
| find and demerge merged cells | Excel Programming | |||
| merged cells problem | Excel Programming | |||
Linear Mode
