Dear all , separately below macro runs very well
Sub FillBlanks()
'With Sheets(2)
For Each vCell In Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Cells
If vCell = "" Then
Range(vCell.Address).Value = Range(vCell.Address).Offset(-1, 0).Value
End If
Next vCell
'End With
End Sub
But if I add another macro together like below
Sub bse()
Sheets(1).Columns("A:A").Copy Sheets(2).Range("B1")
LastRow = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row
Sheets(2).Range("A1").Formula = "=IF(ISNUMBER(SEARCH(""@"",B1)),RIGHT(B1,5),B1)"
Sheets(2).Range("A2").Resize(LastRow).Formula = "=IF(ISNUMBER(SEARCH(""@"",B2)),RIGHT(B2,5),A1)"
Sheets(2).Range("A1:A50000").Value = Sheets(2).Range("A1:A50000").Value
Sheets(2).Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(17, 1), Array(35, 1)), TrailingMinusNumbers _
:=True
'Sub FillBlanks()
'With Sheets(2)
For Each vCell In Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Cells
If vCell = "" Then
Range(vCell.Address).Value = Range(vCell.Address).Offset(-1, 0).Value
End If
Next vCell
'End With
End Sub
Then I got error in the following line
Range(vCell.Address).Value = Range(vCell.Address).Offset(-1, 0).Value
Run-time error ‘1004’
Application-defined or object-defined error
Bookmarks