How to fill Value in Cell if data is not available ?Solved/Closed

Question

Dear All,

Hope you are doing good!

I would like to fill the product name as below in Cell  if the nearby cell value is filled with some other text.

For your reference I have highlighted yellow color in the attached screenshot (Table 2 is an expected behavior).



Can you please help me with this condition using excel formula or VBA code.

Thanks in advance,
Raj

TrowaD · Answer

Hi Raj,

When the products that need to be autofilled are in column B and the headers are in row 1, the following code will do the job:
Sub RunMe()
Dim lRow, lRow2, x As Long

x = Range("C" & Rows.Count).End(xlUp).Row + 1
lRow = Range("B1").End(xlDown).Row

Do
    If Range("B" & lRow + 1) = vbNullString Then
        lRow2 = Range("B" & lRow).End(xlDown).Row
        If lRow2 > x Then lRow2 = x
        Range("B" & lRow).AutoFill Destination:=Range(Cells(lRow, "B"), Cells(lRow2 - 1, "B"))
        lRow = lRow2
    Else
        lRow = lRow + 1
    End If
Loop Until lRow2 = x
End Sub

Best regards,
Trowa

How to fill Value in Cell if data is not available ?

1 response

Similar discussions

Subscribe To Our Newsletter!