+ Reply to Thread
Results 1 to 7 of 7

Increase column letter

  1. #1
    Registered User
    Join Date
    07-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Increase column letter

    Hello, I am trying to increment the column number within a nested for loop, so I don't have to copy this code for each column. Any ideas?

    For r = 1 to row.count
    sheets(1)
    range ("A" & r).select
    Selection.copy
    sheets(2).select
    range ("A" & r).select
    selection.pastespecial
    Next r

    Thank you for the help!

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Increase column letter

    Another loop for s = 1 to column.count using Cells(r,s)
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    07-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Increase column letter

    Thanks Solus. Do I have to use cells(r,s,) with this R1C1 notation, or could I also use Range(r,s)?

  4. #4
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Increase column letter

    You have to use cells(Row,Column) because it uses numbers for the columns instead of letters, so it is easier to increment the loop. The for loop sets s to a numeric value.

  5. #5
    Registered User
    Join Date
    07-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Increase column letter

    That works. Thank you!

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Increase column letter

    No problem. I'm glad I could help. And welcome to the Forum.

    If you are satisfied with the solution please mark the thread as [SOLVED] using the thread tools at the top.

    And if you don't mind adding reputation by clicking the * below the post of anyone who helped you.

  7. #7
    Registered User
    Join Date
    08-21-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Increase column letter

    Hi XeRo Solus,

    I'm hoping you can help as this post is the nearest to resolving my posted problem, which I need urgent help with.


    I have 2 sheets.
    1) Sheet 1 is the source data sheet with survey responses for each question in column format (eg A-Z).
    2) Sheet 2 is an array table which counts the survey resources where multiple criterions are matched (ie using COUNTIF formula).

    The formula is generally the same. Two of the criterions are in row 1 of sheet 2. Only the columns change and they progress consecutively (eg from A to B) for each row of the array table. Please refer to the columns in bold font. As there are 75 survey questions, I would prefer not to copy and paste the recorded VBA formula.

    Below is an example of a formula which I would like to loop and repeat.

    Sheet2.Range("C2").FormulaR1C1 = _
    "=COUNTIFS('Sheet1'!C81,""*""&'Sheet2'!R1C2&""*"",'Sheet1'!C1,'Sheet2'!R1C)"
    Sheet2.Range("C2").AutoFill Destination:=Range("C2:G2"), Type:=xlFillDefault

    Sheet2.Range("C3").FormulaR1C1 = _
    "=COUNTIFS('Sheet1'!C81,""*""&'RPT - Rank'!R1C2&""*"",'Sheet1'!C2,'RPT - Rank'!R1C)"
    Sheet2.Range("C3").AutoFill Destination:=Range("C3:G3"), Type:=xlFillDefault

    etc

    This goes on for 75 columns x 150 plus rows so only a macro will do. I also want to reuse this later so any help would be great.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Getting the column letter to increase when dragging a =countif() function
    By Junior_Excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2013, 04:54 PM
  2. [SOLVED] Increase letter value each loop.
    By Rob* in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2013, 03:17 AM
  3. Replies: 6
    Last Post: 10-13-2005, 05:05 AM
  4. [SOLVED] How to replace column letter in refferences with a function using the old column letter?
    By Dmitry Kopnichev in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-13-2005, 05:05 AM
  5. Replies: 2
    Last Post: 05-09-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1