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.

 

[Michael M]

Hi Pnel
The code I sent to you works fine for me based on your initial request.
I can't do downloads, so can't look at the problem remotely.
What is it doing incorrectly.

 

[Michael M]

Pnel
I've put the exit if the data is already in Column "A" inside the loop.
See if that solves the isssue

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 For
    '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]

The new adjustment did not work either.

I'll try to describe the situation as clearly as possible

I have 3 columns in this exact order:
Col A: LEGACY_AGREEMENT_NO
Col B: ROW ID
Col C: LEGACYAGREEMENT_LINE_ITEM

When I run the macro, Col A should stay where it is (unless elsewhere in the sheet) Col C should move to Col B and ROW ID should be moved to Col C.

However, after running the macro, everything looks the same. I still have the original column order.

My guess is that the loop is terminating early and doesn't get to do the second criteria because the first criteria executes just fine when the column data is not in it's desired position.

Thanks for your patience.

 

[Michael M]

PNel
The problem might be with the fact that you have changed the text in the second required column.
Your initial request was for the second column to be "LEGACY_AGREEMENT_ITEM_NO"
Your current request is "LEGACYAGREEMENT_LINE_ITEM"
If you change the lines in the code to suit it should work ok.
Keep in mind that syntax is critical....if the spelling or description is wrong, you get a wrong result..hope that helps.

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 For
    '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_LINE_ITEM" Then Exit Sub
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACYAGREEMENT_LINE_ITEM" 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]

PNel
The problem might be with the fact that you have changed the text in the second required column.
Your initial request was for the second column to be "LEGACY_AGREEMENT_ITEM_NO"
Your current request is "LEGACYAGREEMENT_LINE_ITEM"
If you change the lines in the code to suit it should work ok.
Keep in mind that syntax is critical....if the spelling or description is wrong, you get a wrong result..hope that helps.

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 For
    '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_LINE_ITEM" Then Exit Sub
    'if row 1 has the text
    If Cells(1, c).Value = "LEGACYAGREEMENT_LINE_ITEM" 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

Thanks, the second code worked. It was actually LEGACYAGREEMENT_ITEM_NO....it's just been a long day and my brain is mush and that is what I typed.

Thanks again for your help!

 

[Michael M]

Glad we got it sorted.
As I said, even adding an underscore into the text will muck things up

Cheers !!