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

Panoos64 · Sep 13, 2019

Yes some cells doesn't contains "-" and is just text. The code should place the number code either exists hyphen or text. Thank you once again!

Yongle · Sep 13, 2019

So are you saying ...

IF C contains a hyphen then ...
... replace the first 4 characters with B
ELSE
... concatenate B and C

mohadin · Sep 13, 2019

Hi
sorry it was my fault
Try this

Code:

Sub xxx()
    ar = Application.Transpose(Range("c4").Resize((Cells(Rows.Count, 3).End(xlUp).Row) - 3))
    br = Application.Transpose(Range("b4").Resize((Cells(Rows.Count, 2).End(xlUp).Row) - 3))
     For i = 1 To UBound(ar)
        x = Split(ar(i), "-")
        If UBound(x) < 1 Then
        ar(i) = br(i) & "-" & ar(i)
        Else
        x(0) = Format(br(i), "0000")
        ar(i) = Join((x), "-")
        End If
    Next
    Range("c4").Resize(UBound(ar)) = Application.Transpose(ar)
End Sub

BTW It takes care of missing hyphen

Panoos64 · Sep 13, 2019

Hi Yongle Yes that right!

Panoos64 · Sep 13, 2019

Thank you so much mohadin, is perfect and works base to my data. Thank you once again for your support by which i resolve my project. Have a great day!

Yongle · Sep 13, 2019

Code:

Sub Panoos64()
    Application.ScreenUpdating = False
    Dim r As Long, x As Long, cel As Range, B As String, C As String
    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
            
            B = Format(cel, "0000")
            Set cel = cel.Offset(, 1)
            On Error Resume Next
            If InStr(cel, "-") > 0 Then C = Mid(cel, InStr(cel, "-"), 99) Else C = "-" & cel
            cel = B & C
            
            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		TEXT1
6	19/08/2019		TEXT2
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)

RESULT

Excel 2016 (Windows) 32 bit

	A	B	C
4	Date	F & B Department	Requisition #
5	19/08/2019	0002	0002-TEXT1
6	19/08/2019	0002	0002-TEXT2
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)

Panoos64 · Sep 13, 2019

Thank you Yongle, is perfect now and works nicely. Thank you for whole support you done for me almost for a whole day. Have a great day!

mohadin · Sep 13, 2019

You are well come
Be Happy

Fullfill blank cells

Panoos64

Well-known Member

Panoos64

Well-known Member

Excel Facts

Yongle

Well-known Member

mohadin

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Yongle

Well-known Member

Panoos64

Well-known Member

mohadin

Well-known Member

Similar threads

Forum statistics

Share this page

Fullfill blank cells

Well-known Member

Well-known Member

Excel Facts

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