For/Next Coding

CallMeJD · Aug 2nd 2005

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

For i = 1 To 9
    Sheets("JD" & i).Select
    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

spog00 · Aug 2nd 2005

Re: For/Next Coding

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

Alastair

CallMeJD · Aug 2nd 2005

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,

Code

Sub Contractors()
Application.ScreenUpdating = False
Dim tab1 As Integer
Dim tab2 As Integer
Dim tab3 As Integer
Dim tab4 As Integer
Dim tab5 As Integer
Dim tab6 As Integer
Dim tab7 As Integer
Dim tab8 As Integer
Dim tab9 As Integer


Dim i As Integer
Dim j As Integer
Dim lr As Long
Dim AllContractors As Integer


    tab1 = [b2]
    tab2 = [c2]
    tab3 = [d2]
    tab4 = [e2]
    tab5 = [f2]
    tab6 = [g2]
    tab7 = [h2]
    tab8 = [i2]
    tab9 = [j2]
    
    JD1 = [b3]
    JD2 = [c3]
    JD3 = [d3]
    JD4 = [e3]
    JD5 = [f3]
    JD6 = [g3]
    JD7 = [h3]
    JD8 = [i3]
    JD9 = [j3]


Application.CalculateFull


AllContractors = tab1 + tab2 + tab3 + tab4 + tab5 + tab6 + tab7 + tab8 + tab9
    
'Clear List'
    Range("A6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete




'here's where my problem is'
For i = 1 To 9
    Sheets("JD" & i).Select
    Range("J5").Select
        For j = 1 To "tab" & j
            Sheets("tab" & j).Select
        Cells.Find(What:="contractor", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    Selection.EntireRow.Copy
    Sheets("Contractors").Select
    lr = LastRow(Sheets("Contractors")) + 1
    Range("A" & lr).Select
    ActiveSheet.Paste
        Next j
Next i




End Sub

Display More

spog00 · Aug 2nd 2005

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

For j = 1 To "tab" & j

From what I understand changing this to

Code

For j = 1 to 9

should be ok.

Alastair

CallMeJD · Aug 2nd 2005

Re: For/Next Coding

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

Code

Sheets("JD" & i).Select

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

spog00 · Aug 2nd 2005

Re: For/Next Coding

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

Alastair

tinyjack · Aug 2nd 2005

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

jindon · Aug 2nd 2005

Re: For/Next Coding

Hi
Is this what you want to do?

Code

Sub Contractors()
    Application.ScreenUpdating = False
    
    Dim i As Integer
    Dim j As Integer
    Dim lr As Long
    Dim AllContractors As Integer
    Dim r As Range
    
    For i = 2 To 10
        AllContractors = AllContractors + Cells(2, i)
    Next
     
    Application.CalculateFull
     
    AllContractors = tab1 + tab2 + tab3 + tab4 + tab5 + tab6 + tab7 + tab8 + tab9
     
     'Clear List'
    Range("A6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
     
     
     'here's where my problem is'
    For i = 2 To 10
        With Sheets(Cells(3, i).Value)
            Set r = .Cells.Find(What:="contractor", After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False).Activate
            If Not r Is Nothing Then
                lr = LastRow(Sheets("Contractors")) + 1
                r.Copy Destination:=Sheets("Contractors").Range("a" & lr)
            End If
        End With
    Next i


End Sub

Display More

For/Next Coding

Participate now!

Share