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

Report
Posts
24
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
April 23, 2021
-
Posts
2735
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 29, 2021
-
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

1 reply

Posts
2735
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 29, 2021
460
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!