Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Fundemental Change in Thinking to Select a Column to Copy and Paste

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Posts
    592
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Fundemental Change in Thinking to Select a Column to Copy and Paste

    Code:
    Range("C4:C5000").Copy
    Range("G4:G5000").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Hello All...
    I use the term Fundamental in the title, because I've always used a Column number to find the column I wanted to work on.
    In the above code, I use Column C because the heading of C4 is the data I wish to copy and move.
    Instead, is there a way to find the heading word in any column to choose that column to work with.
    For example, what if C4 has a title/heading of "Zone". Instead of counting over or using C4, can I have excel search for the term "Zone", regardless of column number, and excel will "know" that is the column I wish to work on?
    I've never done it this way, or if it is possible.
    Thanks for the help
    Last edited by Guzzlr; Mar 20th, 2018 at 02:20 PM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,496
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    How about
    Code:
    Sub FndCopy()
    
       Dim Fnd As Range
       
       Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False)
       If Fnd Is Nothing Then Exit Sub
       Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,009
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    You could use Application.Match to get the column number.
    Code:
    Res = Application.Match("Zone", Rows(4), 0)
    
    If Not IsError(Res) Then
        Intersect(Columns(Res), Range("4:5000")).Copy
        Range("G4").PasteSpecial xlPasteValues
    End If
    If posting code please use code tags.

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Posts
    592
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub FndCopy()
    
       Dim Fnd As Range
       
       Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False)
       If Fnd Is Nothing Then Exit Sub
       Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
    End Sub
    Two questions about this code:
    1. Why all the comma's?
    2. Instead of Exiting the Sub, can we use resume, so the programs keeps running?
    thanks

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,496
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    The commas are the to save typing out the name of the parameters. Have a look here for details https://excelmacromastery.com/excel-vba-find/

    I don't quite follow what you mean in question 2.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    Board Regular
    Join Date
    Apr 2009
    Posts
    592
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    Quote Originally Posted by Fluff View Post
    The commas are the to save typing out the name of the parameters. Have a look here for details https://excelmacromastery.com/excel-vba-find/

    I don't quite follow what you mean in question 2.
    It looks like if the word Zone is not in row 4, then the entire program ends. How can it keep moving if Zone is not in row 4?
    Also, I'll need to find/search for more heading in row 4 in addition to the word Zone. Can I use the same dimension of Fnd with an "And" statement.
    For example:

    Code:
       Dim Fnd As Range
       
       Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False) And Range("4:4").find("Bureau", , , xlWhole, , , False, , False)
       If Fnd Is Nothing Then Resume
       Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
    End Sub
    Thanks

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,496
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    What do you want to do if the word is not found?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    Board Regular
    Join Date
    Apr 2009
    Posts
    592
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    Quote Originally Posted by Fluff View Post
    What do you want to do if the word is not found?
    Continue with the rest of the code in the program.
    Could I not just use the statement of On Error Resume Next?
    Thanks
    Last edited by Guzzlr; Mar 20th, 2018 at 04:02 PM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,496
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    This will copy the column if found & then continue
    Code:
    Sub FndCopy()
    
       Dim Fnd As Range
       
       Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False)
       If Not Fnd Is Nothing Then
          Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
          Range("G4").PasteSpecial Paste:=xlPasteValues
       End If
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Posts
    592
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fundemental Change in Thinking to Select a Column to Copy and Paste

    Quote Originally Posted by Fluff View Post
    This will copy the column if found & then continue
    Code:
    Sub FndCopy()
    
       Dim Fnd As Range
       
       Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False)
       If Not Fnd Is Nothing Then
          Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
          Range("G4").PasteSpecial Paste:=xlPasteValues
       End If
    End Sub
    OK..This is working nicely...Thank you
    Instead of Copy the column, what if I wanted to cut and paste, instead of copy and paste?
    I tried using Cut instead of Copy, and it faulted on me
    Thanks
    Last edited by Guzzlr; Mar 20th, 2018 at 05:00 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •