CODEWITHSUNDEEP
excelvba
stackaccount
stackaccount

Reputation: 5

Excel - Rename cell if row contains certain value

I've seen some similar questions but can't quite find an answer for my situation. If data is found in a row, then Column C of that row will be labeled with that data. But I also want all columns below this row to contain this same label in Column C. Until the next data is found, then I want to repeat the same process for the whole document until every row had data in Column C reflecting the data from its particular section.

I probably did a poor job of explaining that, so here is what I would like to happen. Any help would be greatly appreciated.

[Example of what I want the outcome to be.]

Upvotes: 0

Views: 636

Answers (2)

QHarr
QHarr

Reputation: 84465

Which in code look like:

Sub test()
    Dim rng As Range
    With ActiveSheet
        For Each rng In Intersect(.Columns("C"), .UsedRange)
            If Left$(Trim(rng.Offset(, 1)), 5) = "Class" Then
                rng = rng.Offset(, 1)
            Else
                rng = rng.Offset(-1)
            End If
        Next rng
    End With
End Sub

If you were to use column D to determine the last row to loop to you might use the following (swop the "D" for a different column letter if you want to use a different column to determine how far to loop to get to last row)

Option Explicit

Sub test()
    Dim rng As Range
    With ActiveSheet
        For Each rng In .Range(.Cells(1, "C"), .Cells(.Cells(.Rows.Count, "D").End(xlUp).row, "C"))
            If Left$(Trim(rng.Offset(, 1)), 5) = "Class" Then
                rng = rng.Offset(, 1)
            Else
                rng = rng.Offset(-1)
            End If
        Next rng
    End With
End Sub

Upvotes: 0

Windalfin
Windalfin

Reputation: 255

This is very simple with just IF formula. Here is the pseudocode:

if the sentence start with "class" then 
  refer to cell next to him
else:
  refer to cell on top of him

Formula view

Upvotes: 1

Related Questions