Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Columns as Numbers instead of Letters (Excel 2000 or 2003)

    Hi All ... this is probably an easy one for you guys ...

    NextCol = 6
    For A = 1 To AnimalCT
    Sheets("Input-Group Info").Select
    AnimalID = Range(AnimalColLetter & 25 + A).Value
    Sheets("Background").Select
    Range(NextCol & NextRow).Value = AnimalID
    NextCol = NextCol + 1
    Next A


    I want to take a list in the "Input-Group Info" sheet starting at row 26
    and place the ID in column F (=6), then column G (=7), etc.

    But, I don't know how to convert the 6 to F, 7 to G, etc ...

    Any help would be appreciated ...
    Thanks,
    --cat

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Columns as Numbers instead of Letters (Excel 2000 or 2003)

    I hink you want something like:
    <pre>For A = 1 To AnimalCT
    Sheets("Background").Cells(nextrow, nextcol) = _
    Sheets("Input-Group Info").Range(AnimalColLetter & (25 + A))
    NextCol = NextCol + 1
    Next A
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Columns as Numbers instead of Letters (Excel 2000 or 2003)

    Instead of Range("B3"), you can use Cells(3, 2) (first the row number, then the column number). In your code:

    Cells(NextRow, NextCol) = AnimalID

    By the way, it isn't necessary to switch the active sheet all the time. You can replace the lines

    Sheets("Input-Group Info").Select
    AnimalID = Range(AnimalColLetter & 25 + A).Value
    Sheets("Background").Select
    Range(NextCol & NextRow).Value = AnimalID

    with this one:

    Sheets("Background").Cells(NextRow, NextCol) = Sheets("Input Group Info").Range(AnimalColLetter & 25 + A)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Columns as Numbers instead of Letters (Excel 2000 or 2003)

    I hink so oo! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Columns as Numbers instead of Letters (Excel 2000 or 2003)

    Thank you both every so much.

    I can now "count" columns AND shorten my code tremendously by not selecting the sheet first!!!

    I am learning so much from this site!

    --cat

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Columns as Numbers instead of Letters (Excel 2000 or 2003)

    <img src=/S/razz.gif border=0 alt=razz width=25 height=17>a leas I yped he code righ! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Columns as Numbers instead of Letters (Excel 2000 or 2003)

    It should also speed the code up a bit not having to keep selecting sheets.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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