Right & Split question

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

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.
 
What is the question?

--

HTH

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

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

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

Cheers
JulieD
 
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, " ")
 
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" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, February 28, 2004 4:38 PM
Subject: Re: Right & Split question
 
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)
 
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.
 
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)
 
Many thanks to you all.

Regards.

Bob Phillips said:
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)
Click to expand...
 
Back
Top