| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error on Find
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
| FIND type mismatch error, but don't see it | Excel Worksheet Functions | |||
| Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
| Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
| Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Linear Mode
