|
Type mismatch error on Find
I am attempting to find a cell in a column that contains a certain
string, starting from a known location. Here's what I have done so far: Sub OrganizeSummary() Dim sBegRange As String Dim sEndRange As String Dim rFoundCell As Range Dim rTable As Range Dim sHeading As String Dim sPhase1 As String Dim sPhase2 As String Dim vFindResult As Variant Sheets("Summary").Select 'First, sort the current data Range("A17", "I65536").Select Selection.Sort Key1:=Range("B16"), Key2:=Range("A16"), Key3:=Range("H16") 'Now start creating groupings 'First projects launched since last week sHeading = "Projects Launched Since Last Week" sPhase1 = "1-Launched w/Actuals" Range("B16").Select Set rFoundCell = ActiveCell Set rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Activate If rFoundCell Is Nothing Then ActiveCell.Offset(1, -1).Select ActiveCell.EntireRow.Insert ActiveCell.Formula = "None" ActiveCell.Offset(1, 0).Select ActiveCell.EntireRow.Insert ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Range(ActiveCell.Address, ActiveCell.Offset(0, 7).Select).Select Selection.Merge Selection.HorizontalAlignment = xlLeft ActiveCell.Formula = sHeading Else rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) ActiveCell.EntireRow.Insert ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Range(ActiveCell.Address, ActiveCell.Offset(0, 7).Select).Select Selection.Merge Selection.HorizontalAlignment = xlLeft ActiveCell.Formula = sHeading End If Range("A15").Select End Sub As I step through the code, when I get to: Set rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Activate I get Run-time error '13': Type mismatch I already have a reference to MS VBA Extensibility 5.3, as recommended in other postings related to this issue. Any other suggestions? Thanks! - Pat |
Type mismatch error on Find
|
Type mismatch error on Find
I believe the error is caused by the two Set lines conflicting, trying to
work on code for this, I will try to get it back to you ASAP. " wrote: I am attempting to find a cell in a column that contains a certain string, starting from a known location. Here's what I have done so far: Sub OrganizeSummary() Dim sBegRange As String Dim sEndRange As String Dim rFoundCell As Range Dim rTable As Range Dim sHeading As String Dim sPhase1 As String Dim sPhase2 As String Dim vFindResult As Variant Sheets("Summary").Select 'First, sort the current data Range("A17", "I65536").Select Selection.Sort Key1:=Range("B16"), Key2:=Range("A16"), Key3:=Range("H16") 'Now start creating groupings 'First projects launched since last week sHeading = "Projects Launched Since Last Week" sPhase1 = "1-Launched w/Actuals" Range("B16").Select Set rFoundCell = ActiveCell Set rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Activate If rFoundCell Is Nothing Then ActiveCell.Offset(1, -1).Select ActiveCell.EntireRow.Insert ActiveCell.Formula = "None" ActiveCell.Offset(1, 0).Select ActiveCell.EntireRow.Insert ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Range(ActiveCell.Address, ActiveCell.Offset(0, 7).Select).Select Selection.Merge Selection.HorizontalAlignment = xlLeft ActiveCell.Formula = sHeading Else rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) ActiveCell.EntireRow.Insert ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Range(ActiveCell.Address, ActiveCell.Offset(0, 7).Select).Select Selection.Merge Selection.HorizontalAlignment = xlLeft ActiveCell.Formula = sHeading End If Range("A15").Select End Sub As I step through the code, when I get to: Set rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Activate I get Run-time error '13': Type mismatch I already have a reference to MS VBA Extensibility 5.3, as recommended in other postings related to this issue. Any other suggestions? Thanks! - Pat |
Type mismatch error on Find
Dave was right, of course. Thanks! - Pat
Dave Peterson wrote: Lose the .Activate in this statement. Set rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) wrote: I am attempting to find a cell in a column that contains a certain string, starting from a known location. Here's what I have done so far: Sub OrganizeSummary() Dim sBegRange As String Dim sEndRange As String Dim rFoundCell As Range Dim rTable As Range Dim sHeading As String Dim sPhase1 As String Dim sPhase2 As String Dim vFindResult As Variant Sheets("Summary").Select 'First, sort the current data Range("A17", "I65536").Select Selection.Sort Key1:=Range("B16"), Key2:=Range("A16"), Key3:=Range("H16") 'Now start creating groupings 'First projects launched since last week sHeading = "Projects Launched Since Last Week" sPhase1 = "1-Launched w/Actuals" Range("B16").Select Set rFoundCell = ActiveCell Set rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Activate If rFoundCell Is Nothing Then ActiveCell.Offset(1, -1).Select ActiveCell.EntireRow.Insert ActiveCell.Formula = "None" ActiveCell.Offset(1, 0).Select ActiveCell.EntireRow.Insert ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Range(ActiveCell.Address, ActiveCell.Offset(0, 7).Select).Select Selection.Merge Selection.HorizontalAlignment = xlLeft ActiveCell.Formula = sHeading Else rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) ActiveCell.EntireRow.Insert ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Range(ActiveCell.Address, ActiveCell.Offset(0, 7).Select).Select Selection.Merge Selection.HorizontalAlignment = xlLeft ActiveCell.Formula = sHeading End If Range("A15").Select End Sub As I step through the code, when I get to: Set rFoundCell = Columns(1).Find(What:=sPhase1, After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Activate I get Run-time error '13': Type mismatch I already have a reference to MS VBA Extensibility 5.3, as recommended in other postings related to this issue. Any other suggestions? Thanks! - Pat -- Dave Peterson |
| All times are GMT +1. The time now is 10:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com