Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005

    Unanswered: Copying and pasting cells in Excel using vba

    Hi - please help.
    I've got a whole load of data that are in a spreadsheet. I need to write some code. The data was supplied with organisation name, job title and persons name all down one column. I need to separate it so that they can be in 3 different columns. I've been trying to do this in vba, but can't seem to get it right i.e. copying a range of cells in a row to another range in the same row etc. Can someone please tell me how to do this or suggest a better way of sorting this out?

  2. #2
    Join Date
    Feb 2004
    Excel has a tool for this called 'Text To Columns...' found under the data menu. This parses your text into columns based on a delimiter value you set in the TTColumn Wizard. If you've already determined you need a VB solution for this you need a few basics.

    You'll still need to identify a delimiter in order to parse the text out. Could be fixed width, tab, return, comma... this depends on your data.

    In VB you can use copy paste actions but this is not a very efficient method. You can refer to the Cell or Range directly and use a variable to identify the row and a varaible or array to contain the parts of your parsed text. That would look something like this:

    Cells(2, n) = strVal1
    Cells(3, n) = strVal2
    Cells(4, n) = strVal3

    There's more to it than this but without seeing your data I could write a book and it would not apply to your senario.


Posting Permissions

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