+ Reply to Thread
Results 1 to 10 of 10

Using for loops when referencing

  1. 08-20-2009, 11:34 AM #1
    Operator
    Operator is offline
    Registered User
    Join Date
    08-20-2009
    Location
    Auburn, AL
    MS-Off Ver
    Excel 2003
    Posts
    14

    Smile Using for loops when referencing

    First of all, I'm completely new to both this forum and VBA. I have just done a programming course in java before. I hope you will forgive me if this have already been posted. No to my problem.

    I am tryring to use different projected values to project other subparts of those units, which is done in the sheet mean needed weekly. Tje values are taken from projections. The answer that I get from mean needed weekly should be copied to a third sheet.

    This is the code that I am using right know, but I cant get the for loop to work (or more exactly to be able to place the "i" in my text without making compilation errors).


    Please Login or Register  to view this content.
    Thanks in advance!

    E
    Last edited by Operator; 08-21-2009 at 02:06 PM. Reason: Had to insert code tags
    Reply With QuoteRegister To Reply
  2. 08-20-2009, 11:50 AM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using for loops when referencing

    Welcome to the forum, Operator.

    Please take a few minutes to read the forum rules, and then edit your post to add code tags.

    Thanks.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 08-20-2009, 12:10 PM #3
    Operator
    Operator is offline
    Registered User
    Join Date
    08-20-2009
    Location
    Auburn, AL
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Using for loops when referencing

    Is it other ways to make the code easier to read?
    Reply With QuoteRegister To Reply
  4. 08-20-2009, 12:41 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using for loops when referencing

    Thanks.

    What is this line supposed to do?
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 08-20-2009, 02:10 PM #5
    Operator
    Operator is offline
    Registered User
    Join Date
    08-20-2009
    Location
    Auburn, AL
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Using for loops when referencing

    What is this line supposed to do?

    Code:
    Range((2), (i + 3)).Select
    Its suppose to decide where to paste the selected values (next line.)
    When you paste something in excel the first cell decides where the rest of the values will be pasted.
    As I already have selected which values to copy 
    Please Login or Register  to view this content.
    I just need to paste them somewhere. The line you asking for decides the place where to (and it works fine if the i+3 is changed into 3).
    I want it to post the next values from the next i a column to left of the earlier ones. That's why I tried the for loop.

    i+3 =4 but (when i =1

    Please Login or Register  to view this content.
    but the i in the expression leads to error messages when I execute.
    Reply With QuoteRegister To Reply
  6. 08-20-2009, 02:30 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using for loops when referencing

    The syntax is wrong.

    What cell are you tring to select? What is the loop supposed to do?

    Part of the macros can be simplified like this:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 08-20-2009, 03:30 PM #7
    Operator
    Operator is offline
    Registered User
    Join Date
    08-20-2009
    Location
    Auburn, AL
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Using for loops when referencing

    Okay, the first part selects the input for a sheet that I have made:

    Please Login or Register  to view this content.
    There is 11 input values taken from a row as you could see. When I change i I would like to change where I get the input to the row underneath (i.e row four instead of three when i=1). That's why I tried some ...Projections!R[-4]C[3]" to ...Projections!R[-4+i]C[3]" but as you told me, I have big problems with the syntax and the functions, I am a totally newbie in VBA.

    This is not enough however. The for loop (i) also change where I paste the output from the sheet. The output is taken from the same place as before but pasted at another location. 

    Please Login or Register  to view this content.
    The first time its at C2, but the next time (i=1) I would like to post it at column D.

    This things that I do would perfectly fine with just one loop if it begins in the beginning of the macro.

    Most of the code is taken by the record macro function. The problems start when I have to change it in order to insert a working for loop.

    Best Regars

    E
    Reply With QuoteRegister To Reply
  8. 08-20-2009, 03:45 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using for loops when referencing

    How about posting a workbook and explaining in context?
    Reply With QuoteRegister To Reply
  9. 08-21-2009, 12:03 PM #9
    Operator
    Operator is offline
    Registered User
    Join Date
    08-20-2009
    Location
    Auburn, AL
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Using for loops when referencing

    I resolved one part of it. The upper part.

    This i did with this phrase

    Please Login or Register  to view this content.
    However, I did not solve the last part.


    Please Login or Register  to view this content.
    The upper part works fine but the code beneath gets a run-time error 1004. Paste SPecial method of range class failed.


    Please Login or Register  to view this content.
    Do you have any suggestions? (Some sheets have changed their names, I am aware of that).

    I am sorry for not being able to post the sheet here.
    Last edited by Operator; 08-21-2009 at 12:06 PM.
    Reply With QuoteRegister To Reply
  10. 08-21-2009, 02:05 PM #10
    Operator
    Operator is offline
    Registered User
    Join Date
    08-20-2009
    Location
    Auburn, AL
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Using for loops when referencing

    It's solved Part Pivot was referring to a pivot table. It was suppose to refer to the pivot raw data.


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1