VBA find select all then shift all adjacent cells to the right

Question

I'm trying to write a macro that will sort a work book that is generated by a system at work. I have attempted to chopshop some code together from other posts on this site with no success.

The goal is to search column A for any cells that contain either "IN" or "OUT" then move every thing to the right of these cells one cell to the right.

I have some code that works for the first output but it will only ever ready the first out put I know why it doesn't work but I don't know how to fix it.

Any help would be much appreciated, Thanks,

Sub Data_only()
'
' Reworks_Data_only Macro
'
' Keyboard Shortcut: Ctrl+k
'
    Columns("J:AB").Select
    Selection.ClearContents
    Cells.Select
    Cells.EntireColumn.AutoFit`enter code here`
'   ^ Cuts out unused columns and autofits the rest
    Columns("A:A").Select
    Selection.Find(What:="in", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
'       ^Searches Column A for "IN"
        ActiveCell.Offset(, 1).Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'       ^Selects the found cell and shift the whole row to the right

End Sub

EDIT This is a mock up of the file im looking to change, there would normally be a few hundred batches and a lot more columns but it should be workable. batches mock up

Have you tried recording your own macro and see what Excel gives you? — braX, Sep 15 '17 at 12:38
yes I have that's where I got the search function it was easier to understand than some of the things I read. — Flippant God, Sep 15 '17 at 13:08

score 1 · Accepted Answer · answered Sep 15 '17 at 12:47

Something like that would be possible if you like to use the Find function ...

Option Explicit

Public Sub Data_only()
    MoveByFind "IN"
    MoveByFind "OUT"
End Sub

Public Function MoveByFind(FindString As String)
    Dim Found As Range
    Set Found = Columns("A:A").Find(What:=FindString, LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Not Found Is Nothing Then
        Dim firstAddress As String
        firstAddress = Found.Address 'remember first find for no endless loop

        Do
            Found.Offset(0, 1).Insert Shift:=xlToRight 'move cells right
            Set Found = Columns("A:A").FindNext(After:=Found) 'find next
        Loop While Not Found Is Nothing And Found.Address <> firstAddress 'loop until end or nothing found
    End If
End Function

Thanks this worked a treat on the test I'm going to run it on a real one to see what happens. — Flippant God, Sep 15 '17 at 13:27

Cyril · Answer 2 · 2017-09-15T14:01:50.277

0

You can do this with a simple loop, rather than using the Find function:

Dim i as Long, LR as Long
LR = Cells(Rows.Count,1).End(xlUp).Row
For i = 2 to LR 'Assumes you have a header in row 1
    If Cells(i,1).Value = "IN" OR Cells(i,1).Value = "OUT" Then
        Cells(i,2).Insert Shift:=xlToRight 
    End If
Next i

Note that In and Out are case-sensitive.

You could also do this with the Find function, though you would find all, or use find next, and use the .insert as you've doen in your code.

Edit:

Assuming that the issue is hidden characters, InStr can be used:

Dim i As Long, LR As Long, j As Integer, k As Integer
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LR 'Assumes you have a header in row 1
    j = InStr(Cells(i, 1).Value, "IN")
    k = InStr(Cells(i, 1).Value, "OUT")
    If j > 0 Or k > 0 Then
        Cells(i, 2).Insert Shift:=xlToRight
    End If
Next i

edited Sep 15 '17 at 14:01

answered Sep 15 '17 at 12:41

Cyril

6,448
1
18
31

Note that it is assumed Column A has contiguous data, whereas the last row (LR) can be determined dynamically. – Cyril Sep 15 '17 at 12:42
If you declare `Dim i, LR As Integer` then only the last one is `Integer` but `i` will be `Variant` it's exactly the same like `Dim i As Variant, LR As Integer`. Also [always use `Long` instead of `Integer`](https://stackoverflow.com/a/26409520/3219613) especially when iterating rows. Excel has more rows than `Integer` can handle! Use `Dim i As Long, LR As Long` instead. – Pᴇʜ Sep 15 '17 at 12:49
@Peh I thought it was a matter of memory for Integer versus Long? I know I should be using LR as Long for large data (should have given that for this code). Regarding the *Dim i, LR as Integer*, I was under the impression that everything in that string was considered *As Integer*... apparently that's wrong? – Cyril Sep 15 '17 at 12:55
Thanks for coming back to me I have input the code you have added but it doesn't seem to do anything, I have added a Picture of the file I am using to test. – Flippant God Sep 15 '17 at 13:22
@FlippantGod First comment states that the cases are sensitive; did you change "In" to "IN" and "Out" to "OUT" such as your file shows? – Cyril Sep 15 '17 at 13:25
Yes I did maybe I put something else in wrong either way its solved not but thanks – Flippant God Sep 15 '17 at 13:41
@FlippantGod Glad it was solved. Regarding what I had posted, all I can think is that there are hidden characters, which would prevent this. "IN " versus "IN", etc. – Cyril Sep 15 '17 at 13:49
@Cyril "*apparently that's wrong?*" yes, that's wrong. `Dim i, LR As Integer` is exactly the same as `Dim i As Variant, LR As Integer` (VBA automatically assumes Variant if no type specified for **each** variable). And there is **no matter in memory** for `Integer` versus `Long` as you can read in the [link](https://stackoverflow.com/a/26409520/3219613) I gave already in my comment. Integer is silently converted to Long anyway in VBA. Therefore always use Long. – Pᴇʜ Sep 15 '17 at 15:26

VBA find select all then shift all adjacent cells to the right

2 Answers2