Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Object variable not set"??
I'm using Find in:
Dim MyTarget As String Dim rngSearch As Range Dim thisRow As Long << other stuff thisRow = ActiveCell.Row Set rngSearch = Range("A" & thisRow & ":AA" & thisRow) rngSearch.Select If Selection.Find(What:=MyTarget _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then The IF statement generates the "Object variable or With block variable not set" error. But I use this same code in another macro as: ws1.Activate rngSearch.Select If Selection.Find(What:=strTIR _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then (In this case, there are two workbooks, and I have to activate the one I want to search. In the first code, I am using only one workbook.) Any suggestions? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Object variable not set"??
Ed,
When using Find, you should write your code as follows: Dim FoundCell As Range Set FoundCell = Selection.Find(...) If Not FoundCell Is Nothing Then ' text found, do something with FoundCell Else ' text not found, handle it End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ed" wrote in message ... I'm using Find in: Dim MyTarget As String Dim rngSearch As Range Dim thisRow As Long << other stuff thisRow = ActiveCell.Row Set rngSearch = Range("A" & thisRow & ":AA" & thisRow) rngSearch.Select If Selection.Find(What:=MyTarget _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then The IF statement generates the "Object variable or With block variable not set" error. But I use this same code in another macro as: ws1.Activate rngSearch.Select If Selection.Find(What:=strTIR _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then (In this case, there are two workbooks, and I have to activate the one I want to search. In the first code, I am using only one workbook.) Any suggestions? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Object variable not set"??
Chip, my code now looks like:
thisRow = ActiveCell.Row Set rngSearch = Range("A" & thisRow & ":AA" & thisRow) rngSearch.Select Set rngFound = Selection.Find(What:=MyTarget _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate If rngFound Is Nothing Then Range("A" & thisRow).EntireRow.Hidden = True End If I still get the "Object variable not set" error on the Set rngFound statement??? Ed "Chip Pearson" wrote in message ... Ed, When using Find, you should write your code as follows: Dim FoundCell As Range Set FoundCell = Selection.Find(...) If Not FoundCell Is Nothing Then ' text found, do something with FoundCell Else ' text not found, handle it End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ed" wrote in message ... I'm using Find in: Dim MyTarget As String Dim rngSearch As Range Dim thisRow As Long << other stuff thisRow = ActiveCell.Row Set rngSearch = Range("A" & thisRow & ":AA" & thisRow) rngSearch.Select If Selection.Find(What:=MyTarget _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then The IF statement generates the "Object variable or With block variable not set" error. But I use this same code in another macro as: ws1.Activate rngSearch.Select If Selection.Find(What:=strTIR _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then (In this case, there are two workbooks, and I have to activate the one I want to search. In the first code, I am using only one workbook.) Any suggestions? Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Object variable not set"??
Ed,
Get rid of the Activate at the end of the Find line of code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ed" wrote in message ... Chip, my code now looks like: thisRow = ActiveCell.Row Set rngSearch = Range("A" & thisRow & ":AA" & thisRow) rngSearch.Select Set rngFound = Selection.Find(What:=MyTarget _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate If rngFound Is Nothing Then Range("A" & thisRow).EntireRow.Hidden = True End If I still get the "Object variable not set" error on the Set rngFound statement??? Ed "Chip Pearson" wrote in message ... Ed, When using Find, you should write your code as follows: Dim FoundCell As Range Set FoundCell = Selection.Find(...) If Not FoundCell Is Nothing Then ' text found, do something with FoundCell Else ' text not found, handle it End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ed" wrote in message ... I'm using Find in: Dim MyTarget As String Dim rngSearch As Range Dim thisRow As Long << other stuff thisRow = ActiveCell.Row Set rngSearch = Range("A" & thisRow & ":AA" & thisRow) rngSearch.Select If Selection.Find(What:=MyTarget _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then The IF statement generates the "Object variable or With block variable not set" error. But I use this same code in another macro as: ws1.Activate rngSearch.Select If Selection.Find(What:=strTIR _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then (In this case, there are two workbooks, and I have to activate the one I want to search. In the first code, I am using only one workbook.) Any suggestions? Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Object variable not set"??
Thanks, Chip! That was it!
Ed "Chip Pearson" wrote in message ... Ed, Get rid of the Activate at the end of the Find line of code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ed" wrote in message ... Chip, my code now looks like: thisRow = ActiveCell.Row Set rngSearch = Range("A" & thisRow & ":AA" & thisRow) rngSearch.Select Set rngFound = Selection.Find(What:=MyTarget _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate If rngFound Is Nothing Then Range("A" & thisRow).EntireRow.Hidden = True End If I still get the "Object variable not set" error on the Set rngFound statement??? Ed "Chip Pearson" wrote in message ... Ed, When using Find, you should write your code as follows: Dim FoundCell As Range Set FoundCell = Selection.Find(...) If Not FoundCell Is Nothing Then ' text found, do something with FoundCell Else ' text not found, handle it End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ed" wrote in message ... I'm using Find in: Dim MyTarget As String Dim rngSearch As Range Dim thisRow As Long << other stuff thisRow = ActiveCell.Row Set rngSearch = Range("A" & thisRow & ":AA" & thisRow) rngSearch.Select If Selection.Find(What:=MyTarget _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then The IF statement generates the "Object variable or With block variable not set" error. But I use this same code in another macro as: ws1.Activate rngSearch.Select If Selection.Find(What:=strTIR _ , After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows _ , SearchDirection:=xlNext, _ MatchCase:=False).Activate Then (In this case, there are two workbooks, and I have to activate the one I want to search. In the first code, I am using only one workbook.) Any suggestions? Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why do i "sometimes" need to "edit chart object" before "source da | Charts and Charting in Excel | |||
Excel VBA - Storing text in a variable, and "'Cells' of object _Global failed" | Excel Programming | |||
"Run-time error 91: Object variable or With block not set" | Excel Programming | |||
Ogilvy Help :) - "Object variable or With block variable not set" | Excel Programming | |||
Exel2000 VBA - Creating a Command Bar - Error: "Object Variable or With not Set" | Excel Programming |