Simple: IF statement with current ranges

[PNel]

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

Pseudo code for what I want to do:

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

    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]

bump

 

[Michael M]

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

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]

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]

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

 On Error Resume Next

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

 

[Michael M]

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

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]

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

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]

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

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]

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

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]

Can anyone help with this?

Thanks in advance