0

Hi I need to move a range of cells in each row (scanned) that contains a cell in a specific column (could be any column in my case, but in my code it is colomn "E") which value start by the text string l. Here is what I've tried (I'm new to VBA). In this case I want to move Ji:Mi to Ki:Ni, where i is row number.

Sub Move2()
    For i = 48 To 31 Step -1
        If Range("E" & i) = "*l" Then
            Range("J" & i & ":" & "M" & i).Cut
            Range("K" & i & ":" & "N" & i).Select
            ActiveSheet.Paste
        End If
    Next i
End Sub
NeuroBM
  • 1
  • 1

1 Answers1

0

Try using Like Operator like this:

Sub Move2()
    For i = 48 To 31 Step -1
        If Range("E" & i) Like "l*" Then
            Range("J" & i & ":" & "M" & i).Cut _
            Range("K" & i & ":" & "N" & i)
        End If
    Next i
End Sub

Above code specifically looks for l and will ignore L.
If you need to include both, use this line for the If. HTH.

If Range("E" & i) Like "[Ll]*" Then
L42
  • 19,427
  • 11
  • 44
  • 68
  • Thanks a lot, the Like operator is useful. I had a mistake in "Step 1" which I changed to "Step -1". Corrected. – NeuroBM Nov 19 '14 at 05:49
  • @NeuroBM Ah Ok. I'll leave the answer here anyways :). Adjusted my code as well :D. And forget about using *Select* in most cases. [Check this cool post out](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Also this [link](http://blogs.msmvps.com/jonskeet/2012/11/24/stack-overflow-question-checklist/) for accepting answer in case someone answers your question which satisfies your needs :D – L42 Nov 19 '14 at 05:53