VBA help Populate dynamic row cells if criteria equals xxx.

[htran4]

Hi, I am trying to populate the cells in column C. The range in column A changes every month so i am trying to find a way to dynamically count the amount of rows and then populate the counted cells in column C.

I've attached the picture and current code but can't figure out the rest.


Sub totn_if_example2()

For Each grades In Range("B2:B11")

If grades = "A" Or grades = "B" Then
grades.Offset(0, 1).Value = "Great work"

ElseIf grades = "C" Then
grades.Offset(0, 1).Value = "Needs Improvement"

ElseIf grades = "D" Then
grades.Offset(0, 1).Value = "Time for a Tutor"

Else
grades.Offset(0, 1).Value = " "


End If

Next grades

End Sub

 

[Tom Urtis]

Ignoring for a moment that you did not declare your variables, which you really should do, the direct answer to your question is to replace this
For Each grades In Range("B2:B11")
with this
For Each grades In Range("B2:B" & cells(rows.count, 2).end(xlUp).row)

There is also the issue of inherent redundancy if the list grows to be quite long and you override the pre-existing entries in column C. This is also work-around-able but the code line suggestion handles your immediate concern.

 

[My Aswer Is This]

Try this

Sub My_Grades()
'Modified  11/24/2020  1:03:16 AM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim i As Long

For i = 1 To Lastrow
    With Cells(i, 3)
        Select Case Cells(i, 2).Value

            Case "A", "B": .Value = "Great Work"
            Case "C": .Value = "Needs Improvement"
            Case "D": .Value = "Time for a Tutor"
        Case Else
            .Value = ""

        End Select
    End With
Next

End Sub