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:D 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