IF cell does not contain certain value move to right
 

Hello all.

I am looking for a macro that looks in Col. B and if each cell does contain
anything other than N, S, W, E, NE, NW, SW, SE, WN,WS, ES, EN would move it
to the right.

ex.

Col. A Col. B
1 N
2 Sam
3 W
4 Tom
5 NE


Result:

Col. A Col. B Col. C
1 N
2 Sam
3 W
4 Tom
5 NE




Thx.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1

IF cell does not contain certain value move to right
 

I'm guessing you didn't mean, for example WS and ES... I'm guessing you want
this to be for the standard directional abbreviations (such as, SW and SE).
Assuming that is the case, does this code do what you want?

Dim R As Range
For Each R In Range("B1:B" & CStr(Cells(Rows.Count, 1).End(xlUp).Row))
If Not (R.Value Like "[NSWE]" Or R.Value Like "[NS][EW]") Then
R.Offset(0, 1).Value = R.Value
R.Value = ""
End If
Next

Rick


"saman110 via OfficeKB.com" <u35670@uwe wrote in message
news:7a187cbfec6b4@uwe...
Hello all.

I am looking for a macro that looks in Col. B and if each cell does
contain
anything other than N, S, W, E, NE, NW, SW, SE, WN,WS, ES, EN would move
it
to the right.

ex.

Col. A Col. B
1 N
2 Sam
3 W
4 Tom
5 NE


Result:

Col. A Col. B Col. C
1 N
2 Sam
3 W
4 Tom
5 NE




Thx.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1

IF cell does not contain certain value move to right
 

Sub Move_Value()
Dim rngB As Range
Dim cel As Range
Dim strToFind As String
Dim strValid As String

'String to have both leading and trailing commas for each value
strValid = ",N,S,W,E,NE,NW,SW,SE,WN,WS,ES,EN,"

'NOTE:Cells(2, "B") starts row 2.
'To start row 1 change to Cells(1, "B")
With Sheets("Sheet1") 'Edit sheet name if required
Set rngB = Range(Cells(2, "B"), _
Cells(Rows.Count, "B").End(xlUp))
rngB.Select
End With


For Each cel In rngB
'Create string from cell value with
'leading and trailing commas
strToFind = "," & cel.Value & ","

'Test for existance of string
'If following line returns Zero then not found.
If InStr(1, strValid, strToFind) = 0 Then
'Not found therefore copy to column C
cel.Offset(0, 1) = cel.Value
'Clear value from column B
cel.ClearContents
End If
Next cel

End Sub

Regards,

OssieMac

"saman110 via OfficeKB.com" wrote:

Hello all.

I am looking for a macro that looks in Col. B and if each cell does contain
anything other than N, S, W, E, NE, NW, SW, SE, WN,WS, ES, EN would move it
to the right.

ex.

Col. A Col. B
1 N
2 Sam
3 W
4 Tom
5 NE


Result:

Col. A Col. B Col. C
1 N
2 Sam
3 W
4 Tom
5 NE




Thx.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1