For/Next Coding

  • I have a VBA Code written (I don't think I need to paste it all here... let me know if I'm wrong).


    I have dim'd the following: JD1 through JD9 are Integers. tab1 through tab9 are Integers


    JD1 through JD9 are referenced to cells with my tabnames in them. I then want to cycle through them in my VBA Code. I'm not sure if I need to use the INDIRECT feature, or how to cycle through JD1 through JD9 using the "For/Next" coding. Here's what I have (not working!).



    Code
    1. For i = 1 To 9
    2. Sheets("JD" & i).Select
    3. Range("J5").Select



    Let me know if that doesn't make sense and I'll put all my vba and/or a worksheet reference.


    Thanks everyone!


    JD

  • Re: For/Next Coding


    Post all your code, it sounds like you problem can probably be worked around.


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: For/Next Coding


    It could probably stand to be reduced a bit for simplicity sake, but I just want the section to work that I have indicated below.


    Thanks again,


  • Re: For/Next Coding


    Sorry, maybe I'm being dumb, but there seems no problem in that line. What problem exactly are you having?


    As a side point, this line will cause an error:


    Code
    1. For j = 1 To "tab" & j


    From what I understand changing this to

    Code
    1. For j = 1 to 9

    should be ok.


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: For/Next Coding


    I get the "subscript out of range" error when I try to call the sheet:

    Code
    1. Sheets("JD" & i).Select


    I think there needs to be another way to get the variable JD1 by using the i value. Ideas?

  • Re: For/Next Coding


    This means you do not have a Sheet named JD1 or whatever number it error on.


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: For/Next Coding


    Have a look at Arrays rather than using multiple variables


    [vba]
    Dim JD(1 to 3) As String
    Dim i As Long


    JD(1) = "Sheet1"
    JD(2) = "Sheet2"
    JD(3) = "Sheet3"


    For i = 1 to 3
    Worksheets(JD(i)).Activate
    MsgBox "Sheet " & JD(i) & " open"
    Next i
    [/vba]


    HTH


    TJ

  • Re: For/Next Coding


    Hi
    Is this what you want to do?