VBA VLOOKUP with dynamic range

Question

I am brand-new to VBA.

I have two worksheets in the same workbook. The first worksheet, shStudentInfo, contains all of the information for each of my students, one row per StudentID (B4 in the code). The second worksheet, shSchedData, contains their schedules where there may be 0-14 rows per StudentID, depending on how many courses each student is taking.

I am attempting to use a loop and VLOOKUP with a dynamic range to extract the course name from each row of shSchedData and copy it to the appropriate cell in shStudentInfo, then move down one row. Currently I've hardcoded cell "CO4" as the appropriate cell although I will also need to make that reference move one cell to the right for each pass through the loop.

Here is my inelegant code:

Option Explicit
Dim MyRow As Long

Sub StudentSchedules()

Dim EndRow As Long
Dim MyRng As Range

shSchedData.Activate

'hard code first row of data set
MyRow = 3
'dynamic code last row of data set
EndRow = shSchedData.Range("A1048575").End(xlUp).Row

'create a dynamic range, a single row from shSchedData
Set MyRng = ActiveSheet.Range(Cells(MyRow, 1), Cells(MyRow, 9))

'Loop through entire data set one line at a time
Do While MyRow <= EndRow

    shSchedData.Select
    MyRng = ActiveSheet.Range(Cells(MyRow,1),Cells(MyRow,9))

    shStudentInfo.Select

   'Import course name from shSchedData worksheet
    Range("CO4").Select                                                                                         
    ActiveCell.Clear

    ActiveCell.Formula = "=VLOOKUP(B4,'Schedule Data'!& MyRng,6,0)"    
    'The above line results in a #NAME? error in CO4 of shStudentInfo

    'Also tried:
    'ActiveCell.Formula = "=VLOOKUP(B4,'Schedule Data'!& MyRng.Address,6,0)" 

    'increment counter
    MyRow = MyRow + 1

Loop
End Sub

Answer 1

The following rewrite will get your code working to the extent that its purpose can be determined.

The VLOOKUP formula does not appear correct and in any event, there might be a better method of retrieving the data. However, I cannot determine your end purpose from your narrative or code. Sample data together with expected results would help.

Option Explicit

'I see no reason to put this here
'dim myRow As Long

Sub StudentSchedules()

    Dim myRow, endRow As Long, myRng As Range

    'no need to activate, just With ... End With block it
    With shSchedData

        'assigned a strarting value
        myRow = 3
        'dynamic code last row of data set
        endRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'Loop through entire data set one line at a time
        Do While myRow <= endRow

            'create a dynamic range, a single row from shSchedData
            Set myRng = .Range(.Cells(myRow, 1), .Cells(myRow, 9))

           'Import course name from shSchedData worksheet
            shStudentInfo.Range("CO4").Offset(0, myRow - 3).Formula = _
              "=VLOOKUP(B4, " & myRng.Address(external:=True) & ", 6, false)"

            'increment counter
            myRow = myRow + 1

        Loop
    End With

End Sub