Fullfill blank cells

Panoos64 · Sep 13, 2019

Hi all of you, kindly require to provide me a VBA code so that to run through col. “B” and should fulfill the blank cells with code number, contains in some cells which are random in col. “B” Therefore that should start from row “5” till the last cell which contains data. I present below the initial data and expected result. Thank you all in advance.

Initial data

	A	B	C
4	Date	F & B Department	Requisition #
5	19/08/2019		0000-00975108
6	19/08/2019		0000-00980555
7	19/08/2019		0000-00980650
8	21/08/2019	0002	0000-00981675
9	21/08/2019	0002	0000-00982714
10	21/08/2019	0002	0000-00983125

Expected result

	A	B	C
4	Date	F & B Department	Requisition #
5	19/08/2019	0002	0000-00975108
6	19/08/2019	0002	0000-00980555
7	19/08/2019	0002	0000-00980650
8	21/08/2019	0002	0000-00981675
9	21/08/2019	0002	0000-00982714
10	21/08/2019	0002	0000-00983125

MARK858 · Sep 13, 2019

Assuming that your cells are truly blank and column B are constants then maybe...

Code:

Sub Panoos64()
On Error Resume Next
    With Range("B5:B" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(4)
        .FormulaR1C1 = "=R[1]C"
   End With
 On Error GoTo 0
   
End Sub

Yongle · Sep 13, 2019

Is this what you want ?

BEFORE

Excel 2016 (Windows) 32 bit

	A	B	C
4	Date	F & B Department	Requisition #
5	19/08/2019		0000-00975108
6	19/08/2019		0000-00980555
7	19/08/2019		0000-00980650
8	21/08/2019	0002	0000-00981675
9	21/08/2019	0002	0000-00982714
10	21/08/2019	0002	0000-00983125
11	22/08/2019	0003	0000-00983126
12	23/08/2019	0004	0000-00983127
13	24/08/2019		0000-00983128
14	25/08/2019	0006	0000-00983129
15	26/08/2019		0000-00983130
16	27/08/2019		0000-00983131
17	28/08/2019		0000-00983132
18	29/08/2019	0010	0000-00983133

Sheet: Sheet2

RESULTS
Excel 2016 (Windows) 32 bit

	A	B	C
4	Date	F & B Department	Requisition #
5	19/08/2019	0002	0000-00975108
6	19/08/2019	0002	0000-00980555
7	19/08/2019	0002	0000-00980650
8	21/08/2019	0002	0000-00981675
9	21/08/2019	0002	0000-00982714
10	21/08/2019	0002	0000-00983125
11	22/08/2019	0003	0000-00983126
12	23/08/2019	0004	0000-00983127
13	24/08/2019	0006	0000-00983128
14	25/08/2019	0006	0000-00983129
15	26/08/2019	0010	0000-00983130
16	27/08/2019	0010	0000-00983131
17	28/08/2019	0010	0000-00983132
18	29/08/2019	0010	0000-00983133

Sheet: Sheet2

Code:

[CODE]Sub Panoos64()
    Dim r As Long, cel As Range
    With ActiveSheet
        For r = .Range("A" & .Rows.Count).End(xlUp).Row - 1 To 5 Step -1
            Set cel = .Cells(r, 2)
            If cel = "" Then
                cel.Offset(1).Copy cel
            End If
        Next r
    End With
End Sub

[/CODE]

Panoos64 · Sep 13, 2019

Thank you so MARK!, it works perfect. A further step, i would like to copying the code from col. "B" to "C" the first 4 digits. Please see below the expected result. I express my apologies that i didn't mentioned it at my first post. However thanks once again for your support. Have a great day!

	A	B	C
4	Date	F & B Department	Requisition #
5	19/08/2019	0002	0002-00975108
6	19/08/2019	0002	0002-00980555
7	19/08/2019	0002	0002-00980650
8	21/08/2019	0002	0002-00981675
9	21/08/2019	0002	0002-00982714
10	21/08/2019	0002	0002-00983125

Panoos64 · Sep 13, 2019

Thank you Yongle, it works perfect and base to my data. Please can you see my last post too and i express my apologies that i didn't refer it on my initial post. Thank you once again for your support. Have a nice day!

Yongle · Sep 13, 2019

Code:

Sub Panoos64()
    Dim r As Long, x As Long, cel As Range
    With ActiveSheet
        For r = .Range("A" & .Rows.Count).End(xlUp).Row To 5 Step -1
            Set cel = .Cells(r, 2)
            If x <> 0 And cel = "" Then
                cel.Offset(1).Copy cel
            End If
            Set cel = cel.Offset(, 1)
            cel = cel.Offset(, -1) & Mid(cel, InStr(cel, "-"), 99)
            x = 1
        Next r
    End With
End Sub

BEFORE

Excel 2016 (Windows) 32 bit

	A	B	C
4	Date	F & B Department	Requisition #
5	19/08/2019		0000-00975108
6	19/08/2019		0000-00980555
7	19/08/2019		0000-00980650
8	21/08/2019	0002	0000-00981675
9	21/08/2019	0002	0000-00982714
10	21/08/2019	0002	0000-00983125
11	22/08/2019	0003	0000-00983126
12	23/08/2019	0004	0000-00983127
13	24/08/2019		0000-00983128
14	25/08/2019	0006	0000-00983129
15	26/08/2019		0000-00983130
16	27/08/2019		0000-00983131
17	28/08/2019		0000-00983132
18	29/08/2019	0010	0000-00983133

Sheet: Sheet1 (2)

RESULTS

Excel 2016 (Windows) 32 bit

	A	B	C
4	Date	F & B Department	Requisition #
5	19/08/2019	0002	0002-00975108
6	19/08/2019	0002	0002-00980555
7	19/08/2019	0002	0002-00980650
8	21/08/2019	0002	0002-00981675
9	21/08/2019	0002	0002-00982714
10	21/08/2019	0002	0002-00983125
11	22/08/2019	0003	0003-00983126
12	23/08/2019	0004	0004-00983127
13	24/08/2019	0006	0006-00983128
14	25/08/2019	0006	0006-00983129
15	26/08/2019	0010	0010-00983130
16	27/08/2019	0010	0010-00983131
17	28/08/2019	0010	0010-00983132
18	29/08/2019	0010	0010-00983133

Sheet: Sheet1 (2)

mohadin · Sep 13, 2019

For your second req....

Code:

Sub xxx()
    ar = Application.Transpose(Range("a4").Resize((Cells(Rows.Count, 1).End(xlUp).Row) - 4))
    br = Application.Transpose(Range("b4").Resize((Cells(Rows.Count, 2).End(xlUp).Row) - 4))
    ReDim d(1 To UBound(ar))
    For i = 1 To UBound(ar)
        x = Split(ar(i), "-")
        x(0) = Format(br(i), "0000")
        ar(i) = Join((x), "-")
    Next
    Range("a4").Resize(UBound(d)) = ar
End Sub

Panoos64 · Sep 13, 2019

Hi Yongle and thenk you once again for your support. The code runs but it stop on this command cel = cel.Offset(, -1) & Mid(cel, InStr(cel, "-"), 99)
i think is due to the text which existing in some of the cells in col. "C" and i am apologizing that i didn't mention it. Also the format after adjustment in col. "C" should be like e.g. 0002-00975108, it appear it like 2-00975108. Please can you adjust it so that to run if is number either text too? I appreciated your support and i would like to thank you once again!

Panoos64 · Sep 13, 2019

Hi mohadin and thank you for your interesting to resolve my issue. I would like to mention that the code it doesn't work. It just copying the heading from col. "A" & "B" till the last fulfill cell. Thank you

Yongle · Sep 13, 2019

I understand the format "0000" problem

I do not understand the other problem - are you saying that some cells in column C do not contain a hyphen "-" ?
Should the code ignore those cells ?

Fullfill blank cells

Panoos64

Well-known Member

Excel Facts

MARK858

MrExcel MVP

Yongle

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Yongle

Well-known Member

mohadin

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

Fullfill blank cells

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock