Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #111154 > unrolled thread

Search specific columnheading and copy/insert this column to location column A

Back to article view | Back to microsoft.public.excel.programming

Contents

  Search specific columnheading and copy/insert this column to location column A JS SL <jmslab@xs4all.nl> - 2019-09-29 05:39 -0700
    Re: Search specific columnheading and copy/insert this column to location column A Claus Busch <claus_busch@t-online.de> - 2019-09-29 15:03 +0200
      Re: Search specific columnheading and copy/insert this column to location column A JS SL <jmslab@xs4all.nl> - 2019-09-29 21:43 -0700
      Re: Search specific columnheading and copy/insert this column to location column A GS <gs@v.invalid> - 2019-09-30 12:02 -0400

#111154 — Search specific columnheading and copy/insert this column to location column A

Goodmorning,

In a macro I copy/insert a certain column to A.
This is mostly the same column location, but.... it's not for sure. It should be nice if the macro check this first. It's a bit a nice to have macro :)

Actions;
1) In the active sheet search for the column with in the headingrow (row1) a specific text, like for example 'ABCD'.

2a) If not found this columnheading give a popup message "Columnheading 'ABCD' not exist". Then the macro stops.
2b) If found columnheading 'ABCD' but this is column A then give a popup message "Columnheading 'ABCD' is already placed on A". Then the macro stops.

3) If found, and not placed on location Column A, then copy this whole column and copy/insert this column at the place of column A. Then this become column A and all the other columns will move to the right.


regards, Johan


 

[toc] | [next] | [standalone]

#111156

Hi Johan,

Am Sun, 29 Sep 2019 05:39:49 -0700 (PDT) schrieb JS SL:

> Actions;
> 1) In the active sheet search for the column with in the headingrow (row1) a specific text, like for example 'ABCD'.
> 
> 2a) If not found this columnheading give a popup message "Columnheading 'ABCD' not exist". Then the macro stops.
> 2b) If found columnheading 'ABCD' but this is column A then give a popup message "Columnheading 'ABCD' is already placed on A". Then the macro stops.
> 
> 3) If found, and not placed on location Column A, then copy this whole column and copy/insert this column at the place of column A. Then this become column A and all the other columns will move to the right.

try:

Sub MoveColumn()
Dim c As Range

With ActiveSheet
    Set c = .Range("1:1").Find("ABCD", lookat:=xlPart)
    If c Is Nothing Then
        MsgBox "Columnheading 'ABCD' not exist"
        Exit Sub
    End If
        
    If Not c Is Nothing And c.Column <> 1 Then
        Columns(c.Column).Cut
        Columns(1).Insert Shift:=xlToRight
    ElseIf Not c Is Nothing And c.Column = 1 Then
        MsgBox "Columnheading 'ABCD' is already placed on A"
        Exit Sub
    End If
End With
End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#111160

Great !!! Thx. !!
regards, Johan

[toc] | [prev] | [next] | [standalone]

#111161

A more brief version...

Sub MoveColumn2()
  Dim c As Range

  Set c = ActiveSheet.Range("1:1").Find("ABCD", lookat:=xlPart)
  If Not c Is Nothing Then
    If c.Column = 1 Then
      MsgBox "Heading 'ABCD' is already in column A", vbInformation
    Else
      Columns(c.Column).Cut: Columns(1).Insert Shift:=xlToRight
      MsgBox "Heading 'ABCD' was found and moved to column A", vbInformation
    End If
  Else
    MsgBox "Heading 'ABCD' does not exist", vbExclamation
  End If
End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web