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

On 30 Jul, 15:30, matt wrote:
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

--


Gary


"matt" wrote in message
...
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....

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

"matt" wrote:

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....

tRY

Cells.Find("My String").Offset(, 1).Copy

mIKE

"matt" wrote:

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.



"matt" wrote:

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

--


Gary


"matt" wrote in message
...
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.



"matt" wrote:

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.

"Gary Keramidas" wrote:

one way
Cells.Find("My String").offset(,1).Copy

--


Gary


"matt" wrote in message
...
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....

Maybe

myval = Cells.Find("My String").Offset(, 1).Value
mvval = Left(myval, 4)

Mike

"matt" wrote:

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.



"matt" wrote:

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....

jl's method is how i would normally code to find the string, too, because it
just may not find he string..


--


Gary


"matt" wrote in message
...
That worked perfect Gary. Thanks.
JLG if you read this is there a benefit of doing it your way? It seems more
complicated.

"Gary Keramidas" wrote:

one way
Cells.Find("My String").offset(,1).Copy

--


Gary


"matt" wrote in message
...
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....

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".

"matt" wrote:

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.



"matt" wrote:

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....

They are all basically doing the same thing. I just mad a comple procedure
out of it insteat of a single command line.

"matt" wrote:

That worked perfect Gary. Thanks.
JLG if you read this is there a benefit of doing it your way? It seems more
complicated.

"Gary Keramidas" wrote:

one way
Cells.Find("My String").offset(,1).Copy

--


Gary


"matt" wrote in message
...
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....

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.
--
HTH...

Jim Thomlinson


"matt" wrote:

That worked perfect Gary. Thanks.
JLG if you read this is there a benefit of doing it your way? It seems more
complicated.

"Gary Keramidas" wrote:

one way
Cells.Find("My String").offset(,1).Copy

--


Gary


"matt" wrote in message
...
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....

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

"JLGWhiz" wrote:

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".

"matt" wrote:

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.



"matt" wrote:

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....

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



"matt" wrote in message
...
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

"JLGWhiz" wrote:

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".

"matt" wrote:

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.



"matt" wrote:

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....

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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



"matt" wrote in message
...
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

"JLGWhiz" wrote:

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".

"matt" wrote:

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.



"matt" wrote:

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....

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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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



"matt" wrote in message
...
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

"JLGWhiz" wrote:

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".

"matt" wrote:

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.



"matt" wrote:

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....

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

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

Rick


"matt" wrote in message
...
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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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



"matt" wrote in message
...
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

"JLGWhiz" wrote:

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".

"matt" wrote:

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.



"matt" wrote:

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....

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

"Rick Rothstein (MVP - VB)" wrote:

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

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

Rick


"matt" wrote in message
...
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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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



"matt" wrote in message
...
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

"JLGWhiz" wrote:

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".

"matt" wrote:

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.



"matt" wrote:

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....

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)
--


Gary


"matt" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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

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

Rick


"matt" wrote in message
...
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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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



"matt" wrote in message
...
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

"JLGWhiz" wrote:

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".

"matt" wrote:

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.



"matt" wrote:

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....

I'll give it a try.. thanks!!!!

"Gary Keramidas" wrote:

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)
--


Gary


"matt" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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

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

Rick


"matt" wrote in message
...
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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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



"matt" wrote in message
...
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

"JLGWhiz" wrote:

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".

"matt" wrote:

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.



"matt" wrote:

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....