Simple: IF statement with current ranges

P

PNel

New Member
Joined
Oct 3, 2011
Messages
9
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
Click to expand...


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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Issue with Macros and renaming files
Replies
6
Views
516
kevin9999
K
R
  • Question
Formatting using VBA
Replies
0
Views
429
RandyD123
R
G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
371
rlv01
R
L
  • Solved
VBA SaveAs
Replies
3
Views
277
Joe4
Joe4
C
  • Solved
How to not set a range in VBA
Replies
7
Views
402
Coco1
C

Forum statistics

Threads
1,215,356
Messages
6,124,475
Members
449,164
Latest member
Monchichi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top