 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / July 2008Find a Cell Value
Hi, I figuered out how to search through the sheet and find a string. With something like Cells.Find("My String").Activate How do I have it do a .Copy of the cell to the right of that one. So if it finds the string in "B10" then I want it to Copy "C10". Thanks in advance.... Hi Matt, Once you've got your active cell try: ActiveCell.Offset(0, 1) = ActiveCell 0, 1 is no rows and 1 column (-ves also work) JF > Hi, > I figuered out how to search through the sheet and find a string. With > something like Cells.Find("My String").Activate > How do I have it do a .Copy of the cell to the right of that one. So if it > finds the string in "B10" then I want it to Copy "C10". > Thanks in advance.... one way Cells.Find("My String").offset(,1).Copy  SignatureGary > Hi, > I figuered out how to search through the sheet and find a string. With > something like Cells.Find("My String").Activate > How do I have it do a .Copy of the cell to the right of that one. So if it > finds the string in "B10" then I want it to Copy "C10". > Thanks in advance.... That worked perfect Gary. Thanks. JLG if you read this is there a benefit of doing it your way? It seems more complicated. > one way > Cells.Find("My String").offset(,1).Copy [quoted text clipped - 5 lines] > > finds the string in "B10" then I want it to Copy "C10". > > Thanks in advance.... jl's method is how i would normally code to find the string, too, because it just may not find he string.. SignatureGary > That worked perfect Gary. Thanks. > JLG if you read this is there a benefit of doing it your way? It seems more [quoted text clipped - 9 lines] >> > finds the string in "B10" then I want it to Copy "C10". >> > Thanks in advance.... They are all basically doing the same thing. I just mad a comple procedure out of it insteat of a single command line. > That worked perfect Gary. Thanks. > JLG if you read this is there a benefit of doing it your way? It seems more [quoted text clipped - 9 lines] > > > finds the string in "B10" then I want it to Copy "C10". > > > Thanks in advance.... There is a definite benefit to doing it JLG's way. If the find does not find anything then the code will crash. By assigning the found range to a range object and then checking that the object is not nothing then you avoid the error... There is still one remaining issue with the find and that is that by not specifying all of the parameters the find will use whatever the current values are. If your end user has changed some of the parameters then your code will possibly not find what you ariginally intended... To be safe the code should be more like this... Sub copi() Dim c As Range Set c = Cells.Find(What:="My String", _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If Not c Is Nothing Then c.Offset(0, 1).Copy Sheets("Destination").Range("newRange") End If End Sub The above will not error out if the value is not found and the find will work according to the parameters specified and not the current values of the find parameters. SignatureHTH... Jim Thomlinson > That worked perfect Gary. Thanks. > JLG if you read this is there a benefit of doing it your way? It seems more [quoted text clipped - 9 lines] > > > finds the string in "B10" then I want it to Copy "C10". > > > Thanks in advance.... Sub copi() Dim c As Range myString = Range("A1").Value Set c = Cells.Find("myString", LookIn:=xlValues) If Not c Is Nothing Then c.Offset(0, 1).Copy Sheets("Destination").Range("newRange") End If End Sub > Hi, > I figuered out how to search through the sheet and find a string. With > something like Cells.Find("My String").Activate > How do I have it do a .Copy of the cell to the right of that one. So if it > finds the string in "B10" then I want it to Copy "C10". > Thanks in advance.... Also can I have it remove some numbers to the right, in the column that is next to the string. So it finds the string im searching for say in ("B10"), then I want it to trim the number in ("C10") so there is only four digits left and then do a ".Copy" of that cell. > Hi, > I figuered out how to search through the sheet and find a string. With > something like Cells.Find("My String").Activate > How do I have it do a .Copy of the cell to the right of that one. So if it > finds the string in "B10" then I want it to Copy "C10". > Thanks in advance.... just to add to your post:. dim mystr as string mystr = left(Cells.Find("My String").offset(,1),4) then do what you want with mystr SignatureGary > Also can I have it remove some numbers to the right, in the column that is > next to the string. [quoted text clipped - 8 lines] >> finds the string in "B10" then I want it to Copy "C10". >> Thanks in advance.... Maybe myval = Cells.Find("My String").Offset(, 1).Value mvval = Left(myval, 4) Mike > Also can I have it remove some numbers to the right, in the column that is > next to the string. [quoted text clipped - 8 lines] > > finds the string in "B10" then I want it to Copy "C10". > > Thanks in advance.... I would not try to use the Copy method for this. Something like this might be more efficient. Sub GetPartOfValue() Dim myString As String, c As Range Set c = Cells.Find(myString, LookIn:=xlValues) If Not c Is Nothing Then modVal = Left(c.Offset(0, 1).Value, 4) Sheets("Destination").Range("newRange") = modVal End If Emd Sub This finds the myString variable, assigns the first four characters of the value in the cell to the right of myString to a variable, then assigns that value to a newRange cell in the Destination sheet. You would substitute the actual sheet name and Range address for "Destination" and "newRange". > Also can I have it remove some numbers to the right, in the column that is > next to the string. [quoted text clipped - 8 lines] > > finds the string in "B10" then I want it to Copy "C10". > > Thanks in advance.... Thanks these all worked great!!!! There is another problem now. There is another value that I find and then get the offset of one but the number value in the cell looks like this including spaces: i.e... "$ 11,234,500" Is there a way to remove the "$ " and the four spaces after the dollar sign. Then I need the first four numbers. I tried LTrim and some other things but found out LTrim only removes spaces in the very beginning. Thanks, Matt > I would not try to use the Copy method for this. Something like this might > be more efficient. [quoted text clipped - 25 lines] > > > finds the string in "B10" then I want it to Copy "C10". > > > Thanks in advance.... Are there **always** four spaces between the dollar sign and first digit? If so, this will return the first four digits of your value... Mid(YourValue, 6, 4) If the spaces can vary, then try this... Left(Trim(Mid(YourValue, 2)), 4) Rick > Thanks these all worked great!!!! > There is another problem now. There is another value that I find and then [quoted text clipped - 49 lines] >> > > finds the string in "B10" then I want it to Copy "C10". >> > > Thanks in advance.... Whoops... I missed seeing the commas. Try one of these instead... For constant 4 spaces ============================ Mid(CLng(YourValue), 6, 4) or Mid(Replace(YourValue, ",", ""), 6, 4) For variable number of spaces ============================ Left(CLng(Trim(Mid(YourValue, 2))), 4) or Left(Trim(Mid(Replace(YourValue, ",", ""), 2)), 4) Rick > Are there **always** four spaces between the dollar sign and first digit? > If so, this will return the first four digits of your value... [quoted text clipped - 60 lines] >>> > > finds the string in "B10" then I want it to Copy "C10". >>> > > Thanks in advance.... Thanks for the code Rick. But one of the Developers at my work gave me a hand, guy is a whizzz. He had ended up having to write his own trim function in like a couple of minutes. And when we were steping through the code, one of the "spaces" was not really a space, we did a watch on each character that the trim was going to remove and one of them returned to us an Ascii value of 160 which in the cell looks like a space but it was not recogniving it as one. I think this is because when I saved this file as an Excel file the origional was not .xls. But all is well. Thanks everyone. > Whoops... I missed seeing the commas. Try one of these instead... > [quoted text clipped - 80 lines] > >>> > > finds the string in "B10" then I want it to Copy "C10". > >>> > > Thanks in advance.... Left(CLng(Trim(Mid(Replace(YourValue, Chr(160), ""), 2))), 4) Left(Trim(Mid(Replace(Replace(YourValue, ",", ""), Chr(160), ""), 2)), 4) Rick > Thanks for the code Rick. But one of the Developers at my work gave me a > hand, guy is a whizzz. He had ended up having to write his own trim [quoted text clipped - 103 lines] >> >>> > > finds the string in "B10" then I want it to Copy "C10". >> >>> > > Thanks in advance.... This is the trim function I have and it seems to be working: Private Const WEIRD_SPACE = 160 ___________________________________________________ My other code stuff, where I call the myTrim Function ___________________________________________________ Function myTrim(s As String) As String On Error Resume Next Dim i As Integer Dim s2 As String Dim temp As String For i = 1 To Len(s) temp = Mid(s, i, 1) If temp <> " " And temp <> "$" And Asc(temp) <> WEIRD_SPACE Then s2 = s2 & Mid(s, i, 1) End If Next Err.Clear myTrim = s2 End Function > Left(CLng(Trim(Mid(Replace(YourValue, Chr(160), ""), 2))), 4) > [quoted text clipped - 109 lines] > >> >>> > > finds the string in "B10" then I want it to Copy "C10". > >> >>> > > Thanks in advance.... why not just use this If Not temp Like "*[!0-9]*" Then instead of If temp <> " " And temp <> "$" And Asc(temp) <> WEIRD_SPACE Then you only want the numbers, anyway. (assist to rick) SignatureGary > This is the trim function I have and it seems to be working: > [quoted text clipped - 133 lines] >> >> >>> > > finds the string in "B10" then I want it to Copy "C10". >> >> >>> > > Thanks in advance.... I'll give it a try.. thanks!!!! > why not just use this > [quoted text clipped - 144 lines] > >> >> >>> > > finds the string in "B10" then I want it to Copy "C10". > >> >> >>> > > Thanks in advance.... tRY Cells.Find("My String").Offset(, 1).Copy mIKE > Hi, > I figuered out how to search through the sheet and find a string. With > something like Cells.Find("My String").Activate > How do I have it do a .Copy of the cell to the right of that one. So if it > finds the string in "B10" then I want it to Copy "C10". > Thanks in advance.... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.