Macro - If cell contains xxx move text to different cell

[steh30]

A B C

00:00:00 Text1
00:05:00 Text2
00:10:00 Text3
00:13:00 Text4

Basically I need to search through column A, and any time that isn't in 5 minute increments, such as the 00:13:00, move "Text4" to column C.

Is it possible to search the last 3 characters from the right, for 0:00 or 5:00 using Range("A" & i).value ?

My VBA isn't the best.

Cheers

 

[VoG]

Try this

Sub MoveTime()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If Minute(.Value) Mod 5 <> 0 Then .Offset(, 1).Cut Destination:=.Offset(, 2)
    End With
Next i
End Sub

 

[Zack Barresse]

Hi there,

Not sure what you mean by search through column A, but I'm going to assume you want to look at the currently active sheet, and all cells with data in it excluding row 1 as a header row. That being the case, you might want to take a look at the Mod operator...


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TestTimeVals()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">CLng</SPAN>(Right(Format(c.value, "h:mm"), 2)) Mod 5 <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                c.Offset(0, 2).value = c.Offset(0, 1).value: c.Offset(0, 1).ClearContents <SPAN style="color:#007F00">'change value only</SPAN><br><SPAN style="color:#007F00">'                c.Offset(0, 1).Insert Shift:=xlToRight 'insert, shifting val to right</SPAN><br><SPAN style="color:#007F00">'                c.Offset(0, 1).Cut c.Offset(0, 2) 'cut/paste data</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


As you can see there are two other options that are commented out, depending on how you wanted to actually get the value from B into C.

HTH

 

[mirabeau]

Sub verymoving()
Dim e As Range
For Each e In [a1].CurrentRegion.Resize(, 1)
If IsNumeric(e) Then
    If (e(2) - e < 0.00346) + (e(2) - e > 0.00348) Then
        e(2).Offset(, 1).Cut e(2).Offset(, 2)
    End If
End If
Next e
End Sub