Simple: IF statement with current ranges

PNel · Oct 3, 2011

This should be simple, but I can't figure it out.

Pseudo code for what I want to do:

Code:

IF current selection (column) <> Column A
THEN Paste Current Selection to the left of Column A
ELSE break and move on to next statement

What I am trying to do is search for a column heading, select and cut that column and then paste it in a certain location such as Column A. However, if that selection is already in Column A, it will fail. Hence, I need an IF ELSE but I am no VBA wizard.

Code I already have

Code:

    With Range("1:1")
        'First Move
        .Find("LEGACY_AGREEMENT_NO").EntireColumn.Cut
        Columns("A:A").Select
        Selection.Insert Shift:=xlToLeft
 
        'Next Move
        .Find("LEGACYAGREEMENT_ITEM_NO").EntireColumn.Cut
        Columns("C:C").Select
        Selection.Insert Shift:=xlToLeft
End With

Thanks for any and all help.

PNel · Oct 3, 2011

Michael M · Oct 3, 2011

Hi PNel and welcome to the Board.
This should do what you are after

Code:

Sub moov()
Dim lc As Integer, c As Integer
'find last column
lc = Cells(1, Columns.Count).End(xlToLeft).Column
'start loop from last column
For c = lc To 1 Step -1
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACY_AGREEMENT_NO" Then
    'cut the column out
    Columns(c).EntireColumn.Cut
    'put it before column "A"
    Columns("A:A").Insert Shift:=xlToRight
    'now exit
    Exit Sub
    End If
'check another column
Next c
End Sub

PNel · Oct 3, 2011

Thanks for your response, however I'm still getting a Run-Time error.

The code works fine when cell contents aren't in Column A, but when it is, the error occurs (Run-Time 1004) stating that you can't paste the cut cells onto themselves basically. Is there some sort of error handling that could be used to ignore and move to the next string to find in the loop?

Also, how would I structure that code to search for the next item and repeat the loop? For instance, to search for LEGACY_AGREEMENT_ITEM_NO and move it to column B.

Thanks again.
P

EDIT: And thanks for commenting your code. Much appreciated.

PNel · Oct 3, 2011

I eliminated the error handling issue by incorporating this code at the top of the Sub.

Code:

 On Error Resume Next

EDIT: The article which I got that information from
http://www.cpearson.com/excel/errorhandling.htm

Michael M · Oct 3, 2011

While the method is correct, it's better to try and eliminate the cause, so you could also try

Code:

Sub moov()
Dim lc As Integer, c As Integer
'find last column
lc = Cells(1, Columns.Count).End(xlToLeft).Column
'start loop from last column
For c = lc To 1 Step -1
    'if value is already in col "A" then exit
    If Cells(1, 1).Value = "LEGACY_AGREEMENT_NO" Then Exit Sub
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACY_AGREEMENT_NO" Then
    'cut the column out
    Columns(c).EntireColumn.Cut
    'put it before column "A"
    Columns("A:A").Insert Shift:=xlToRight
    'now exit
    Exit Sub
    End If
'check another column
Next c
End Sub

PNel · Oct 3, 2011

Michael M said:

While the method is correct, it's better to try and eliminate the cause, so you could also try

Code:

Sub moov()
Dim lc As Integer, c As Integer
'find last column
lc = Cells(1, Columns.Count).End(xlToLeft).Column
'start loop from last column
For c = lc To 1 Step -1
    'if value is already in col "A" then exit
    If Cells(1, 1).Value = "LEGACY_AGREEMENT_NO" Then Exit Sub
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACY_AGREEMENT_NO" Then
    'cut the column out
    Columns(c).EntireColumn.Cut
    'put it before column "A"
    Columns("A:A").Insert Shift:=xlToRight
    'now exit
    Exit Sub
    End If
'check another column
Next c
End Sub

Thanks! This code works and eliminates the error when the contents are already in the first column.

How would I structure this to search for the next item and repeat the process? IE: To search for LEGACYAGREEMENT_ITEM_NO and place it in column B?

Best Regards!

Michael M · Oct 3, 2011

There will be a far more elegant solution, but this will do the trick

Code:

Sub moov()
Dim lc As Integer, c As Integer
'find last column
lc = Cells(1, Columns.Count).End(xlToLeft).Column
    'if value is already in col "A" then exit
    If Cells(1, 1).Value = "LEGACY_AGREEMENT_NO" Then Exit Sub
'start loop from last column
For c = lc To 1 Step -1
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACY_AGREEMENT_NO" Then
    'cut the column out
    Columns(c).EntireColumn.Cut
    'put it before column "A"
    Columns("A:A").Insert Shift:=xlToRight
    'now exit
    Exit For 'jumps out of loop if completed
    End If
 Next c
'search for next item
For c = lc To 2 Step -1
'if value is already in col "B" then exit
    If Cells(1, 2).Value = "LEGACY_AGREEMENT_ITEM_NO" Then Exit Sub
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACY_AGREEMENT_ITEM_NO" Then
    'cut the column out
    Columns(c).EntireColumn.Cut
    'put it before column "B"
    Columns("B:B").Insert Shift:=xlToRight
    'now exit
    Exit Sub
    End If 'check another column
Next c
End Sub

PNel · Oct 4, 2011

Thanks again for the reply.

The macro still isn't operating correctly. I realized I had given you the wrong column name so I corrected that (see code below) but it still doesn't move the item from column C to B.

Perhaps the loop is exiting prematurely?

I've attached a link to a sample of the data that I am using in my workbook as well as the corrected macro code below. Any help is appreciated.

http://dl.dropbox.com/u/4050227/Find and Move SAMPLE DATA.xlsx

Excel 2007

Code:

Sub moov()
Dim lc As Integer, c As Integer
'find last column
lc = Cells(1, Columns.Count).End(xlToLeft).Column
    'if value is already in col "A" then exit
    If Cells(1, 1).Value = "LEGACY_AGREEMENT_NO" Then Exit Sub
'start loop from last column
For c = lc To 1 Step -1
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACY_AGREEMENT_NO" Then
    'cut the column out
    Columns(c).EntireColumn.Cut
    'put it before column "A"
    Columns("A:A").Insert Shift:=xlToRight
    'now exit
    Exit For 'jumps out of loop if completed
    End If
 Next c
'search for next item
For c = lc To 2 Step -1
'if value is already in col "B" then exit
    If Cells(1, 2).Value = "LEGACYAGREEMENT_ITEM_NO" Then Exit Sub
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACYAGREEMENT_ITEM_NO" Then
    'cut the column out
    Columns(c).EntireColumn.Cut
    'put it before column "B"
    Columns("B:B").Insert Shift:=xlToRight
    'now exit
    Exit Sub
    End If 'check another column
Next c
End Sub

PNel · Oct 4, 2011

Can anyone help with this?

Thanks in advance

Simple: IF statement with current ranges

PNel

New Member

Excel Facts

PNel

New Member

Michael M

Well-known Member

PNel

New Member

PNel

New Member

Michael M

Well-known Member

PNel

New Member

Michael M

Well-known Member

PNel

New Member

PNel

New Member

Similar threads

Forum statistics

Share this page

Simple: IF statement with current ranges

New Member

Excel Facts

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock