Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Issue? WorksheetFuntion.Search
I am searching in a Loop through multiple lines of data and looking for
conditions. There are at least 3 possible conditions for my fist pass through. 1. Search finds "defeated" 2. Search finds "you" 3. Search finds neither of the above I have the following variables: Parse_Text = CStr(Range("A1").Value) Find_Text = "you" I am performing the following task: AttackNoun = Application.WorksheetFunction.Search(Find_Text, Parse_Text) The issue occurs when it doesn't find "defeated", it errors out to debug mode. -------------------------------------- Entire code snippet thus far Sub Test_Parse() ' ' Test_Parse Macro ' Start of a test Macro to Parse data with rules ' ' Keyboard Shortcut: Ctrl+Shift+G ' ' Select the proper Sheet and Cell A1 Sheets("play1").Select Range("A1").Select ' First part of routine goes through and counts the number of rows of data that have data in them ' For now I am just putting the number of the counter in B1 (for debugging purposes). That number will be used in the next section of code 'NOTE THAT THIS PART WORKS FINE AND IS HERE FOR COMPLETENESS Counter = 1 StrCounter = CStr(Counter) Parse_Text = CStr(Range("A1").Value) Do While Parse_Text < "" Range("A" + StrCounter).Select Parse_Text = CStr(Range("A" + StrCounter).Value) Counter = Counter + 1 StrCounter = CStr(Counter) Loop Range("B1").Select Range("B1").Value = (Counter - 1) ' Here I am just setting a variable to equal the number of rows to parse Num_Rows = Counter - 2 StrNum_Rows = CStr(Num_Rows) ' Now we need to start a Loop to look through the rows, one at a time, for Num_Rows number of Rows Counter = 2 'Reset the counter to 1 so we can tell when we are at the final row StrCounter = CStr(Counter) Parse_Text = CStr(Range("A1").Value) Do While Counter < Num_Rows - 1 Range("A" + StrCounter).Select 'select the first/next row of data to parse Parse_Text = CStr(Range("A" + StrCounter).Value) 'MsgBox Parse_Text 'MsgBox Find_Text 'First order of understanding -- Is this a "Deafeated" Line 'If so, then skip line Find_Text = "defeated" ' NOTE THAT THIS IS WHERE IT ERRORS OUT IF THE WORD "defeat" IS NOT IN THE TEXT AttackNoun = Application.WorksheetFunction.Search(Find_Text, Parse_Text) If AttackNoun 1 Then 'Do something Else 'Second order of understanding -- Are you doing something or is someone doing something to you? Find_Text = "You" AttackNoun = Application.WorksheetFunction.Search(Find_Text, Parse_Text) If AttackNoun = 1 Then Result_Text = CStr("You do something to something") Range("C" + StrCounter).Select Range("C" + StrCounter) = Result_Text End If If AttackNoun < 1 Then Result_Text = CStr("Something does something to you") Range("C" + StrCounter).Select Range("C" + StrCounter) = Result_Text End If Counter = Counter + 1 StrCounter = CStr(Counter) End If Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Issue? WorksheetFuntion.Search
Don
It's difficult to follow what you are trying to accomplish. Post back and tell us what you have (the layout of your data, i.e., data in these rows and those columns). Then tell us what you want to do. You say you want to search for some words, "defeated" and "you", and also search for the absence of both words. Tell us what you want to happen when each condition is found. Do this in plain words, not in VBA code. Post back and you will get plenty of help. HTH Otto "Don" wrote in message ... I am searching in a Loop through multiple lines of data and looking for conditions. There are at least 3 possible conditions for my fist pass through. 1. Search finds "defeated" 2. Search finds "you" 3. Search finds neither of the above I have the following variables: Parse_Text = CStr(Range("A1").Value) Find_Text = "you" I am performing the following task: AttackNoun = Application.WorksheetFunction.Search(Find_Text, Parse_Text) The issue occurs when it doesn't find "defeated", it errors out to debug mode. -------------------------------------- Entire code snippet thus far Sub Test_Parse() ' ' Test_Parse Macro ' Start of a test Macro to Parse data with rules ' ' Keyboard Shortcut: Ctrl+Shift+G ' ' Select the proper Sheet and Cell A1 Sheets("play1").Select Range("A1").Select ' First part of routine goes through and counts the number of rows of data that have data in them ' For now I am just putting the number of the counter in B1 (for debugging purposes). That number will be used in the next section of code 'NOTE THAT THIS PART WORKS FINE AND IS HERE FOR COMPLETENESS Counter = 1 StrCounter = CStr(Counter) Parse_Text = CStr(Range("A1").Value) Do While Parse_Text < "" Range("A" + StrCounter).Select Parse_Text = CStr(Range("A" + StrCounter).Value) Counter = Counter + 1 StrCounter = CStr(Counter) Loop Range("B1").Select Range("B1").Value = (Counter - 1) ' Here I am just setting a variable to equal the number of rows to parse Num_Rows = Counter - 2 StrNum_Rows = CStr(Num_Rows) ' Now we need to start a Loop to look through the rows, one at a time, for Num_Rows number of Rows Counter = 2 'Reset the counter to 1 so we can tell when we are at the final row StrCounter = CStr(Counter) Parse_Text = CStr(Range("A1").Value) Do While Counter < Num_Rows - 1 Range("A" + StrCounter).Select 'select the first/next row of data to parse Parse_Text = CStr(Range("A" + StrCounter).Value) 'MsgBox Parse_Text 'MsgBox Find_Text 'First order of understanding -- Is this a "Deafeated" Line 'If so, then skip line Find_Text = "defeated" ' NOTE THAT THIS IS WHERE IT ERRORS OUT IF THE WORD "defeat" IS NOT IN THE TEXT AttackNoun = Application.WorksheetFunction.Search(Find_Text, Parse_Text) If AttackNoun 1 Then 'Do something Else 'Second order of understanding -- Are you doing something or is someone doing something to you? Find_Text = "You" AttackNoun = Application.WorksheetFunction.Search(Find_Text, Parse_Text) If AttackNoun = 1 Then Result_Text = CStr("You do something to something") Range("C" + StrCounter).Select Range("C" + StrCounter) = Result_Text End If If AttackNoun < 1 Then Result_Text = CStr("Something does something to you") Range("C" + StrCounter).Select Range("C" + StrCounter) = Result_Text End If Counter = Counter + 1 StrCounter = CStr(Counter) End If Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trapping #VALUE! error | Excel Discussion (Misc queries) | |||
while deleting rows it finds an error - error trapping | Excel Programming | |||
Error trapping | Excel Programming | |||
Error trapping | Excel Programming | |||
trapping error | Excel Programming |