Find a value in a column
I'm trying to run the following Find command in VBA:
sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Once I find the text, I need to parse out the contents of the cell, but currently the text is not being found. I know the text is there and I have tried recording a macro to see what steps Excel would use. I also know that I lose the Intelli-Sense when I add the .Row onto it. If there's a better way to do it, please let me know. Thanx! -- TFWBWY...A |
Find a value in a column
Bryan,
It works fine for me. Are you sure that the item in column C doesn't have trailing spaces in it, which would cause a mis-match. -- HTH Bob Phillips "Bryan Dickerson" wrote in message ... I'm trying to run the following Find command in VBA: sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Once I find the text, I need to parse out the contents of the cell, but currently the text is not being found. I know the text is there and I have tried recording a macro to see what steps Excel would use. I also know that I lose the Intelli-Sense when I add the .Row onto it. If there's a better way to do it, please let me know. Thanx! -- TFWBWY...A |
Find a value in a column
xlpart should take care of the trailing spaces--but not embedded spaces--or
other typos! Bob Phillips wrote: Bryan, It works fine for me. Are you sure that the item in column C doesn't have trailing spaces in it, which would cause a mis-match. -- HTH Bob Phillips "Bryan Dickerson" wrote in message ... I'm trying to run the following Find command in VBA: sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Once I find the text, I need to parse out the contents of the cell, but currently the text is not being found. I know the text is there and I have tried recording a macro to see what steps Excel would use. I also know that I lose the Intelli-Sense when I add the .Row onto it. If there's a better way to do it, please let me know. Thanx! -- TFWBWY...A -- Dave Peterson |
Find a value in a column
"eligible for rebate" is actually the start of the phrase in the contents of
the cell--the full contents might be something like "Eligible for rebate $600 if order by 12/01/05". I made sure that the MatchCase is false so that if they don't capitalize it, then it should still be found. I tried searching for just "for rebate" and still nothing. I keep thinking it's the ".Row" property on the end, but if it works for you guys, then maybe it is acceptable. What other things would you guys try to see what is wrong or to perhaps do it another way? "Dave Peterson" wrote in message ... xlpart should take care of the trailing spaces--but not embedded spaces--or other typos! Bob Phillips wrote: Bryan, It works fine for me. Are you sure that the item in column C doesn't have trailing spaces in it, which would cause a mis-match. -- HTH Bob Phillips "Bryan Dickerson" wrote in message ... I'm trying to run the following Find command in VBA: sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Once I find the text, I need to parse out the contents of the cell, but currently the text is not being found. I know the text is there and I have tried recording a macro to see what steps Excel would use. I also know that I lose the Intelli-Sense when I add the .Row onto it. If there's a better way to do it, please let me know. Thanx! -- TFWBWY...A -- Dave Peterson |
Find a value in a column
As much of a revelation as it is, I found something that works, but I'm not
too secure with it and that is shortening the string to just "eligible". I'm not too secure with it because in the example I was working with just a few rows down there was another cell that also had "eligible" in it, so I would feel better if I could get "eligible for rebate" to work, but I will go with this for now. I would still appreciate any comments that anyone might have. "Bryan Dickerson" wrote in message ... "eligible for rebate" is actually the start of the phrase in the contents of the cell--the full contents might be something like "Eligible for rebate $600 if order by 12/01/05". I made sure that the MatchCase is false so that if they don't capitalize it, then it should still be found. I tried searching for just "for rebate" and still nothing. I keep thinking it's the ".Row" property on the end, but if it works for you guys, then maybe it is acceptable. What other things would you guys try to see what is wrong or to perhaps do it another way? "Dave Peterson" wrote in message ... xlpart should take care of the trailing spaces--but not embedded spaces--or other typos! Bob Phillips wrote: Bryan, It works fine for me. Are you sure that the item in column C doesn't have trailing spaces in it, which would cause a mis-match. -- HTH Bob Phillips "Bryan Dickerson" wrote in message ... I'm trying to run the following Find command in VBA: sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Once I find the text, I need to parse out the contents of the cell, but currently the text is not being found. I know the text is there and I have tried recording a macro to see what steps Excel would use. I also know that I lose the Intelli-Sense when I add the .Row onto it. If there's a better way to do it, please let me know. Thanx! -- TFWBWY...A -- Dave Peterson |
Find a value in a column
Is that "eligible for rebate" actually in the cell?
It's not part of some custom formatting, is it? Can you find one of those cells and put this in an adjacent (empty cell): =countif(c99,"*eligible for rebate*") (change c99 to the cell's address) What do you get? I'm still guessing it's a typo! Bryan Dickerson wrote: As much of a revelation as it is, I found something that works, but I'm not too secure with it and that is shortening the string to just "eligible". I'm not too secure with it because in the example I was working with just a few rows down there was another cell that also had "eligible" in it, so I would feel better if I could get "eligible for rebate" to work, but I will go with this for now. I would still appreciate any comments that anyone might have. "Bryan Dickerson" wrote in message ... "eligible for rebate" is actually the start of the phrase in the contents of the cell--the full contents might be something like "Eligible for rebate $600 if order by 12/01/05". I made sure that the MatchCase is false so that if they don't capitalize it, then it should still be found. I tried searching for just "for rebate" and still nothing. I keep thinking it's the ".Row" property on the end, but if it works for you guys, then maybe it is acceptable. What other things would you guys try to see what is wrong or to perhaps do it another way? "Dave Peterson" wrote in message ... xlpart should take care of the trailing spaces--but not embedded spaces--or other typos! Bob Phillips wrote: Bryan, It works fine for me. Are you sure that the item in column C doesn't have trailing spaces in it, which would cause a mis-match. -- HTH Bob Phillips "Bryan Dickerson" wrote in message ... I'm trying to run the following Find command in VBA: sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Once I find the text, I need to parse out the contents of the cell, but currently the text is not being found. I know the text is there and I have tried recording a macro to see what steps Excel would use. I also know that I lose the Intelli-Sense when I add the .Row onto it. If there's a better way to do it, please let me know. Thanx! -- TFWBWY...A -- Dave Peterson -- Dave Peterson |
Find a value in a column
I get a right-justified "1" (without the quotes, of course). What does that
mean? "Dave Peterson" wrote in message ... Is that "eligible for rebate" actually in the cell? It's not part of some custom formatting, is it? Can you find one of those cells and put this in an adjacent (empty cell): =countif(c99,"*eligible for rebate*") (change c99 to the cell's address) What do you get? I'm still guessing it's a typo! Bryan Dickerson wrote: As much of a revelation as it is, I found something that works, but I'm not too secure with it and that is shortening the string to just "eligible". I'm not too secure with it because in the example I was working with just a few rows down there was another cell that also had "eligible" in it, so I would feel better if I could get "eligible for rebate" to work, but I will go with this for now. I would still appreciate any comments that anyone might have. "Bryan Dickerson" wrote in message ... "eligible for rebate" is actually the start of the phrase in the contents of the cell--the full contents might be something like "Eligible for rebate $600 if order by 12/01/05". I made sure that the MatchCase is false so that if they don't capitalize it, then it should still be found. I tried searching for just "for rebate" and still nothing. I keep thinking it's the ".Row" property on the end, but if it works for you guys, then maybe it is acceptable. What other things would you guys try to see what is wrong or to perhaps do it another way? "Dave Peterson" wrote in message ... xlpart should take care of the trailing spaces--but not embedded spaces--or other typos! Bob Phillips wrote: Bryan, It works fine for me. Are you sure that the item in column C doesn't have trailing spaces in it, which would cause a mis-match. -- HTH Bob Phillips "Bryan Dickerson" wrote in message ... I'm trying to run the following Find command in VBA: sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Once I find the text, I need to parse out the contents of the cell, but currently the text is not being found. I know the text is there and I have tried recording a macro to see what steps Excel would use. I also know that I lose the Intelli-Sense when I add the .Row onto it. If there's a better way to do it, please let me know. Thanx! -- TFWBWY...A -- Dave Peterson -- Dave Peterson |
Find a value in a column
That means that "eligible for rebate" is in that cell.
And all my theories about typos goes flying out the window!. Maybe it's the way you wrote the code. sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", _ After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Could cause errors if oWksht isn't the activesheet or the activecell isn't in column C. I think I'd use something like this: Option Explicit Sub testme() Dim sERRow As Long Dim FoundCell As Range Dim oWkSht As Worksheet Dim myStr As String myStr = "eligible for rebate" Set oWkSht = Worksheets("Sheet1") With oWkSht.Range("C:C") Set FoundCell = .Cells.Find(What:=myStr, _ After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) End With If FoundCell Is Nothing Then MsgBox "Oh, oh. What happens here?" Exit Sub '??? End If sERRow = FoundCell.Row MsgBox sERRow End Sub Bryan Dickerson wrote: I get a right-justified "1" (without the quotes, of course). What does that mean? "Dave Peterson" wrote in message ... Is that "eligible for rebate" actually in the cell? It's not part of some custom formatting, is it? Can you find one of those cells and put this in an adjacent (empty cell): =countif(c99,"*eligible for rebate*") (change c99 to the cell's address) What do you get? I'm still guessing it's a typo! Bryan Dickerson wrote: As much of a revelation as it is, I found something that works, but I'm not too secure with it and that is shortening the string to just "eligible". I'm not too secure with it because in the example I was working with just a few rows down there was another cell that also had "eligible" in it, so I would feel better if I could get "eligible for rebate" to work, but I will go with this for now. I would still appreciate any comments that anyone might have. "Bryan Dickerson" wrote in message ... "eligible for rebate" is actually the start of the phrase in the contents of the cell--the full contents might be something like "Eligible for rebate $600 if order by 12/01/05". I made sure that the MatchCase is false so that if they don't capitalize it, then it should still be found. I tried searching for just "for rebate" and still nothing. I keep thinking it's the ".Row" property on the end, but if it works for you guys, then maybe it is acceptable. What other things would you guys try to see what is wrong or to perhaps do it another way? "Dave Peterson" wrote in message ... xlpart should take care of the trailing spaces--but not embedded spaces--or other typos! Bob Phillips wrote: Bryan, It works fine for me. Are you sure that the item in column C doesn't have trailing spaces in it, which would cause a mis-match. -- HTH Bob Phillips "Bryan Dickerson" wrote in message ... I'm trying to run the following Find command in VBA: sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Row Once I find the text, I need to parse out the contents of the cell, but currently the text is not being found. I know the text is there and I have tried recording a macro to see what steps Excel would use. I also know that I lose the Intelli-Sense when I add the .Row onto it. If there's a better way to do it, please let me know. Thanx! -- TFWBWY...A -- Dave Peterson -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 04:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com