need help turning macro into loop

[Carlen]

I am new to VBA and trying to convert the following code to work on a loop from a range of variable length. The range will start A column of the sheet starting on A6 but will end depending on how many variables (rows) are present.

This is the main code:

Sub Run()

'

' Run Macro

' Teacher 1

 Dim nRow As Long
 Dim nStart As Long, nEnd As Long

' Figure out where the range should start.
For nRow = 1 To 100000
If Range("A" & nRow).Value = ThisWorkbook.Sheets("Teachers").Range("A6") Then
nStart = nRow
Exit For
End If
Next nRow


' Figure out where the range should end.
For nRow = nStart To 100000
If Range("A" & nRow).Value = ThisWorkbook.Sheets("Teachers").Range("A7") Then
nEnd = nRow
Exit For
End If
Next nRow
nEnd = nEnd - 1


' Copy and paste selected range.
Range("A" & nStart & ":D" & nEnd).Select
Selection.Copy
ThisWorkbook.Sheets("Calcie").Activate
Range("A6").Select
ActiveSheet.Paste


' Copy output and paste in Output
Range("B4", "W4").Select
Selection.Copy
ThisWorkbook.Sheets("Output").Activate
Range("A5").PasteSpecial Paste:=xlPasteValues

End Sub

In this case the program is selecting the cells between the teacher names listed in spots A6 and A7 of the teachers sheets, pasting into Calcie, copying the output and pasting it Output under A5. Essentially I am trying to make a loop that would go to A7 & A8 of teachers than paste in A6 in output and so on until the end of the teacher list.

Any help here would be greatly appreciated!

 

[chirayu]

You'll need to add a Do Until or a Do While loop at the start

Do Until Activecell.Value = ""
 
'My code
 
Loop

Do While Activecell.Value <> ""
 
'My code
 
Loop

 

[Debraj]

Hi Carlen,
Welcome to the forum.

Please before posting, spend some time here..
http://chandoo.org/forum/forums/new-users-please-start-here.14/

with A sample file, you may got better answer..

 

[NARAYANK991]

Hi Carlen ,

As already posted you will get a more relevant solution if you post a sample workbook ; in the meantime , you can try this code :

Sub Run()
' Run Macro
Dim nRow As Long
Dim nStart As Long, nEnd As Long
Dim i As Long, j As Long

Do

' Figure out where the range should start.
    For nRow = 1 To 100000
        If Range("A" & nRow).Value = ThisWorkbook.Sheets("Teachers").Range("A6").Offset(i).Value Then
            nStart = nRow
            Exit For
        End If
    Next nRow

' Figure out where the range should end.
    For nRow = nStart To 100000
        If Range("A" & nRow).Value = ThisWorkbook.Sheets("Teachers").Range("A7").Offset(i).Value Then
            nEnd = nRow
            Exit For
        End If
    Next nRow
    nEnd = nEnd - 1

' Copy and paste selected range.
    Range("A" & nStart & ":D" & nEnd).Select
    Selection.Copy
    ThisWorkbook.Sheets("Calcie").Activate
    Range("A6").Offset(j).Select
    ActiveSheet.Paste

' Copy output and paste in Output
    Range("B4", "W4").Select
    Selection.Copy
    ThisWorkbook.Sheets("Output").Activate
    Range("A5").Offset(i).PasteSpecial Paste:=xlPasteValues
   
    i = i + 1
    j = j + nEnd - nStart + 1
Loop Until i >= 1000
End Sub

You will have to change the following statement for your file :

Loop Until i >= 1000

Secondly , the way you are finding out the range for a particular teacher , there will be a problem when you come to the entry for the last teacher , since there will be no teacher entry after this ; hence the logic for finding out nEnd will fail. You need to do something about this.

Narayan

 

[Carlen]

Hi Narayan,
Thanks so much. I added the Text "END" to the last row in the column to handle the ending issue. It worked perfectly!