Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula, move to previous cell when the current cell=0 or empty | Excel Discussion (Misc queries) | |||
How do I make my arrow buttons move from cell to cell in Excel? | Excel Discussion (Misc queries) | |||
Excel - arrow keys move whole sheet instead of from cell to cell | Excel Discussion (Misc queries) | |||
arrow keys move entire sheet instead of cell to cell | Excel Worksheet Functions | |||
Arrow keys move screen instead of moving from cell to cell. | Setting up and Configuration of Excel |