Find a Cell Value

  • Thread starter Thread starter matt
  • Start date Start date
M

matt

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
 
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
 
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.
 
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
 
That worked perfect Gary. Thanks.
JLG if you read this is there a benefit of doing it your way? It seems more
complicated.
 
jl's method is how i would normally code to find the string, too, because it
just may not find he string..
 
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".
 
They are all basically doing the same thing. I just mad a comple procedure
out of it insteat of a single command line.
 
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.
 
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
 
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
 
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
 
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.
 
Left(CLng(Trim(Mid(Replace(YourValue, Chr(160), ""), 2))), 4)

Left(Trim(Mid(Replace(Replace(YourValue, ",", ""), Chr(160), ""), 2)), 4)

Rick
 
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
 
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)
 
Back
Top