I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004

What is the question?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004

Hi Stuart

how about something like this:
If Right(C, 2) = "m2" Then
C = Left(C, Len(C) - 2)

this will over type the value in E with the original value minus the m2 bit

Cheers
JulieD

"Stuart" wrote in message
...
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004

Hi Stuart
what is your actual question :-)
Do not see an error in your code. Though I would change
If Right(C, 2) = "m2" Then
x = Split(C, " ")

to
If Right(C.value, 2) = "m2" Then
x = Split(C.value, " ")


--
Regards
Frank Kabel
Frankfurt, Germany

Stuart wrote:
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004

Ideally I need to test each cell as follows:

if the last 2 character are "m2" then copy it to col I, same row
and strip it from the 'found' cell.

Many similar checks to run, but if you would be kind enough
to example one, maybe I can solve those remaining

Regards.

----- Original Message -----
From: "Bob Phillips"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, February 28, 2004 4:38 PM
Subject: Right & Split question


What is the question?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...

"Stuart" wrote in message
...
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004

Stuart,

Here's some code

Dim C As Range

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
Select Case LCase(Right(C, 2))
Case "m2":
C.Value = Left(C.Value, Len(C.Value) - 2)
C.Cells(, 9 - C.Column + 1) = "m2"
'Case "something else":
End Select
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
Ideally I need to test each cell as follows:

if the last 2 character are "m2" then copy it to col I, same row
and strip it from the 'found' cell.

Many similar checks to run, but if you would be kind enough
to example one, maybe I can solve those remaining

Regards.

----- Original Message -----
From: "Bob Phillips"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, February 28, 2004 4:38 PM
Subject: Right & Split question


What is the question?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...

"Stuart" wrote in message
...
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004

Many thanks, big help.

In the other tests, as far as I can see, the test will similarly
involve testing the end of the string in col E, but for
a different length of characters.

So I may have to test for last char 'm'
or say 'tonne'.

Is there a way to incorporate these conditions into the
Case construct, please?

regards.

"Bob Phillips" wrote in message
...
Stuart,

Here's some code

Dim C As Range

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
Select Case LCase(Right(C, 2))
Case "m2":
C.Value = Left(C.Value, Len(C.Value) - 2)
C.Cells(, 9 - C.Column + 1) = "m2"
'Case "something else":
End Select
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
Ideally I need to test each cell as follows:

if the last 2 character are "m2" then copy it to col I, same row
and strip it from the 'found' cell.

Many similar checks to run, but if you would be kind enough
to example one, maybe I can solve those remaining

Regards.

----- Original Message -----
From: "Bob Phillips"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, February 28, 2004 4:38 PM
Subject: Right & Split question


What is the question?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...

"Stuart" wrote in message
...
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004

Stuart,

I think this is it

Dim C As Range

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
Select Case True
Case LCase(Right(C, 2)) = "m2":
C.Value = Left(C.Value, Len(C.Value) - 2)
C.Cells(, 9 - C.Column + 1) = "m2"
'Case "LCase(Right(C, 1)) = "something else":
End Select
Next
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
Many thanks, big help.

In the other tests, as far as I can see, the test will similarly
involve testing the end of the string in col E, but for
a different length of characters.

So I may have to test for last char 'm'
or say 'tonne'.

Is there a way to incorporate these conditions into the
Case construct, please?

regards.

"Bob Phillips" wrote in message
...
Stuart,

Here's some code

Dim C As Range

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
Select Case LCase(Right(C, 2))
Case "m2":
C.Value = Left(C.Value, Len(C.Value) - 2)
C.Cells(, 9 - C.Column + 1) = "m2"
'Case "something else":
End Select
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
Ideally I need to test each cell as follows:

if the last 2 character are "m2" then copy it to col I, same row
and strip it from the 'found' cell.

Many similar checks to run, but if you would be kind enough
to example one, maybe I can solve those remaining

Regards.

----- Original Message -----
From: "Bob Phillips"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, February 28, 2004 4:38 PM
Subject: Right & Split question


What is the question?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...

"Stuart" wrote in message
...
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004

Many thanks to you all.

Regards.

"Bob Phillips" wrote in message
...
Stuart,

I think this is it

Dim C As Range

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
Select Case True
Case LCase(Right(C, 2)) = "m2":
C.Value = Left(C.Value, Len(C.Value) - 2)
C.Cells(, 9 - C.Column + 1) = "m2"
'Case "LCase(Right(C, 1)) = "something else":
End Select
Next
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
Many thanks, big help.

In the other tests, as far as I can see, the test will similarly
involve testing the end of the string in col E, but for
a different length of characters.

So I may have to test for last char 'm'
or say 'tonne'.

Is there a way to incorporate these conditions into the
Case construct, please?

regards.

"Bob Phillips" wrote in message
...
Stuart,

Here's some code

Dim C As Range

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
Select Case LCase(Right(C, 2))
Case "m2":
C.Value = Left(C.Value, Len(C.Value) - 2)
C.Cells(, 9 - C.Column + 1) = "m2"
'Case "something else":
End Select
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
Ideally I need to test each cell as follows:

if the last 2 character are "m2" then copy it to col I, same row
and strip it from the 'found' cell.

Many similar checks to run, but if you would be kind enough
to example one, maybe I can solve those remaining

Regards.

----- Original Message -----
From: "Bob Phillips"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, February 28, 2004 4:38 PM
Subject: Right & Split question


What is the question?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...

"Stuart" wrote in message
...
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date:
26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004