Good Evening all the experts out there. Need help in VBA Excel macro in for each - for next looping

[Rahul Soni]

I am working on a free school project and trying to build an automated system for assigning substitue teachers. I have done alsmot everything but need help in For Each - for next loop to plug in teachers if someone is absent on a given day. The code I wrote so far is looping but not assigning the values one by one. I know something smaller is missing hence need your kind help.

What I need is that : for each red highlighted cell on Time Table sheet - below the highlighted cell a teacher name should be populated from Info sheet column f and it should continue till all the red highlighted cells have been populated with the unique names from Info sheet column F.

Sub setasignee()

Dim rSH As Worksheet

Dim sSH As Worksheet



Set rSH = ThisWorkbook.Sheets("Time Table")
Set sSH = ThisWorkbook.Sheets("Info")

Set Rng = rSH.Range("D5:AU64")


'a = sSH.Range("F" & Rows.Count).End(xlUp).Row



'Dim fName As String








For a = 2 To sSH.Range("F" & Rows.Count).End(xlUp).Row

fName = sSH.Range("F" & a).Value

For Each c In Rng

If c.Interior.ColorIndex = 3 And c.Value <> "" Then



c.Offset(1, 0).Value = fName

'If c.Offset(1, 0).Value <> "" Then Exit For






End If


Next c


Next a



'Next a





End Sub

 

[Alex Blakenburg]

You're welcome. Glad I could help.
If you're list of names it long then the next step would be to convert the Names range to an array to speed it up.