VBA For Next loops and naming variables within them (a few fundamentals)

[dellehurley]

HI
I am Google and Youtube trained when it comes to VBA so my basics can be a little lacking, so my apologise in advance.
I am writing a troubleshooting procedure for a database and I have a couple of questions about For Next To loops when if comes to best practice as well as if there are issues I have not foreseen with reusing the same terms. See the code below...

For i = 2 To lastrow        'does the j need to be changed up for each loop (to the k for eg.) or
    For j = 1 To 3           'only if they use the same range or
        'Do something        or I can keep using the j as the loop ends between uses
    Next j

    For k = 4 To 6
        'do something
    Next k
Next i
   
For i = 2 To lastrow   'or if totally separate this this do the i and j need to be changed
    For j = 1 To 3      'each time or fine to continue forever.
        'Do something
    Next j
Next i
   
'If name a variable(?) like this within a loop like the top one with i loop containing many sub loops
'does this need to declared for each loop or only the once?
For i = 2 To lastrow
    For j = 2 To 10
    FileName = ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value
    Next j
    For j = 2 To 10
    FileName = ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value
    Next j
   
Next i
End Sub

\
Many Thanks for the clarification and help.
Dannielle

 

[offthelip]

I don't really understand your questions but I have modified your code to demonstrate what is actually happening. So I suggest you run this modified code with breakpoints set on each line with "Next i"
Open the immediate window and look at it after each iteration of i. To run to the next breakpoint hit function key F5 or menu RUN/continue. Hopefully this will answer your questions about looping .
To answer your question about declaring a variable you only need to declare a variable once, you can reuse any existing varaible as many times as you like.

Sub test()
lastrow = 5
For i = 2 To lastrow        'does the j need to be changed up for each loop (to the k for eg.) or
    For j = 1 To 3           'only if they use the same range or
        Debug.Print ("i=" & i & " j=" & j & " k=" & k)       '  or I can keep using the j as the loop ends between uses
    Next j

    For k = 4 To 6
        Debug.Print ("i=" & i & " j=" & j & " K=" & k)       '  or I can keep using the j as the loop ends between uses
        'do something
    Next k
Next i    ' set breakpoint here
   
For i = 2 To lastrow   'or if totally separate this this do the i and j need to be changed
    For j = 1 To 3      'each time or fine to continue forever.
        Debug.Print ("i=" & i & " j=" & j & " k=" & k)       '  or I can keep using the j as the loop ends between uses
    Next j

Next i    'and here
   
'If name a variable(?) like this within a loop like the top one with i loop containing many sub loops
'does this need to declared for each loop or only the once?
For i = 2 To lastrow
    For j = 2 To 10
    Filename = ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value
        Debug.Print ("i=" & i & " j=" & j & " k=" & k)       '  or I can keep using the j as the loop ends between uses
    Next j
    For j = 2 To 10
        Debug.Print ("i=" & i & " j=" & j & " k=" & k)       '  or I can keep using the j as the loop ends between uses
    Filename = ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value
    Next j

Next i   ' and here
End Sub

 

[dellehurley]

I don't really understand your questions but I have modified your code to demonstrate what is actually happening. So I suggest you run this modified code with breakpoints set on each line with "Next i"
Open the immediate window and look at it after each iteration of i. To run to the next breakpoint hit function key F5 or menu RUN/continue. Hopefully this will answer your questions about looping .
To answer your question about declaring a variable you only need to declare a variable once, you can reuse any existing varaible as many times as you like.

Sub test()
lastrow = 5
For i = 2 To lastrow        'does the j need to be changed up for each loop (to the k for eg.) or
    For j = 1 To 3           'only if they use the same range or
        Debug.Print ("i=" & i & " j=" & j & " k=" & k)       '  or I can keep using the j as the loop ends between uses
    Next j

    For k = 4 To 6
        Debug.Print ("i=" & i & " j=" & j & " K=" & k)       '  or I can keep using the j as the loop ends between uses
        'do something
    Next k
Next i    ' set breakpoint here
  
For i = 2 To lastrow   'or if totally separate this this do the i and j need to be changed
    For j = 1 To 3      'each time or fine to continue forever.
        Debug.Print ("i=" & i & " j=" & j & " k=" & k)       '  or I can keep using the j as the loop ends between uses
    Next j

Next i    'and here
  
'If name a variable(?) like this within a loop like the top one with i loop containing many sub loops
'does this need to declared for each loop or only the once?
For i = 2 To lastrow
    For j = 2 To 10
    Filename = ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value
        Debug.Print ("i=" & i & " j=" & j & " k=" & k)       '  or I can keep using the j as the loop ends between uses
    Next j
    For j = 2 To 10
        Debug.Print ("i=" & i & " j=" & j & " k=" & k)       '  or I can keep using the j as the loop ends between uses
    Filename = ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value
    Next j

Next i   ' and here
End Sub

Thanks. I know if was a strange and simple question. When I began I presumed you had to change the letter for each loop in the same procedure. Then I decided this may not be the case and I changed my habit. I was just concerned that there may have been an another issue that I had not for seen and thought it best to ask. Same with the variable within a loop.
Again thanks for your time.
Dannielle