If cell is empty equal value from offset(1,4)

[mperrah]

Hello Team,

I have a range (B:B) with some blank rows.
There is data in columns A:C then E are blank then F has data I want to pull from
The data in F is a formula stripping just the first string up to the first space of column B
In column F one row down, I have a value I want entered in the blank rows of Column B

This is what I came up with but it doesn't seem to do anything

Sub Test()

    Dim Rng As Range
    Dim t As Range
    Dim lr As Long
    
    lr = Range("A1").CurrentRegion.Rows.Count
    Set Rng = Range("B2:B" & lr)


    For Each t In Rng
        If t = "" Then
            t.Value = t.Offset(-1, 4).Value
        End If
    Next t
End Sub

I don't get an error, just no change takes place.
Any help is greatly appreciated.

Mark

[mperrah]

Sub Test()

    Dim Rng As Range
    Dim t As Range
    Dim lr As Long
    
    lr = Range("A1").CurrentRegion.Rows.Count
    Set Rng = Range("B2:B" & lr)


    For Each t In Rng
        ' If Range("B" & t).Value = "" Then
        If t.Value = "" Then
            Range("B" & t).Value = Range("B" & t).Offset(-1, 4).Value ' This erros on Method of Range object Global failed
        End If
    Next t
End Sub

[mperrah]

Was able to get this to do what I was hoping for:
Found I was counting rows top down and it hung on blanks.
Used last row up count seemed to do the trick.

Sub FillBlankRowHeaders()   
   Dim Rng As Range
   Dim lr As Long
   
   lr = Range("A65536").End(xlUp).Row
   
   For Each Rng In Range("B2:B" & lr)
        If Rng.Value = "" Then
            Rng.Value = Rng.Offset(1, 4).Value
        End If
   Next Rng
End Sub

[Paul_Hossler]

This should be faster since there's less looping

I used Offset row = 1 (Post#3) instead of offset row = -1 (Posts #1 and #2)

 Range("B" & t).Value = Range("B" & t).Offset(-1, 4).Value ' This errors on Method of Range object Global failed

t is a Range, but you're treating it like a number. You might have meant

 Range("B" & t.Row).Value = Range("B" & t.Row).Offset(-1, 4).Value

Option Explicit


Sub FillBlankRowHeaders()
   Dim Rng As Range, Cll As Range
   
    With ActiveSheet
        On Error GoTo NiceExit
        Set Rng = Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
    End With
        
    For Each Cll In Rng.Cells
        Cll.Value = Cll.Offset(1, 4).Value
    Next
   
NiceExit:
End Sub

[mperrah]

Perfect Paul, Thank you,

I had a feeling i was getting an error due to object type mismatch. I tried a few variations but could not get it straight.
Your solution work perfectly.

Cheers

[jolivanes]

Or another non loop possibility.

Sub Or_So()
With Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    .SpecialCells(4).FormulaR1C1 = "=R[1]C[4]"
    .Value = .Value
End With
End Sub