Another type of Fill

[mole999]

I was provide this blindlingly fast filler (by Fluff)

Sub Mole999()
    Dim Rng As Range
    
    For Each Rng In Range("Y:Y").SpecialCells(xlBlanks).Areas
        If LCase(Rng.Offset(-1).Resize(1).Value) = "on" Then Rng.Value = "ON"
    Next Rng
End Sub

I now have a stock control sheet that like a pivot only provides the first instance of a label until the type changes and needs to adapt what is done

So the area to check is down column B, I've processed the sheet to put the first label at B1 "LabelA", the next label LabelB appears at B198 so need to fill between B1 to B197, then do for the next label.

I get the last row value from C which currently 3754

would
If LCase(Rng.Offset(-1).Resize(1).Value) = "labelA" Then Rng.Value = "LabelA"
If LCase(Rng.Offset(-1).Resize(1).Value) = "labelB" Then Rng.Value = "LabelB"

be efficient (subject to no new labels added)? I think I can kludge it but that's not ideal, any tweaks?

 

[jasonb75]

If 'LabelX' is literal then perhaps something like

If Left(LCase(Rng.Offset(-1).Resize(1).Value), 5)= "label" Then Rng.Value = "Label" & UCase(Mid(Rng.Offset(-1).Resize(1).Value, 6, 1)

If you need to list different 'labels' then select case would probably be more efficient than a long list of If's.

 

[mole999]

If 'LabelX' is literal then perhaps something like

If Left(LCase(Rng.Offset(-1).Resize(1).Value), 5)= "label" Then Rng.Value = "Label" & UCase(Mid(Rng.Offset(-1).Resize(1).Value, 6, 1)

If you need to list different 'labels' then select case would probably be more efficient than a long list of If's.

Thanks Jason, I soldiered on with what I had and just created the static ones at the moment, the Label is not literal it could be anything, and fell into the trap my references weren't lower case, not expecting them to change too often, just wondered if it could be more human proof

 

[jasonb75]

How about if you use the theory of

UCase(Left(x, 1)) & LCase(Mid(x, 2, Len(x) - 2)) & UCase(Right(x, 1))

Where x refers to the cell with the original label?

 

[mole999]

for the ones I had it was quicker to put in as all lower case, I can see how that could work being tweaked :D