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

 [TABLE="class: MsoNormalTable, width: 219"]
<tbody>[TR]
[TD="width: 28"][/TD]
[TD="width: 113"]

A

[/TD]
[TD="width: 108"]

B

[/TD]
[TD="width: 115"]

C

[/TD]
[/TR]
[TR]
[TD="width: 28"]

4

[/TD]
[TD="width: 113"]

Date

[/TD]
[TD="width: 108"]

F & B Department

[/TD]
[TD="width: 115"]

Requisition #

[/TD]
[/TR]
[TR]
[TD="width: 28"]

5

[/TD]
[TD="width: 113"]

19/08/2019

[/TD]
[TD="width: 108"][/TD]
[TD="width: 115"]

0000-00975108

[/TD]
[/TR]
[TR]
[TD="width: 28"]

6

[/TD]
[TD="width: 113"]

19/08/2019

[/TD]
[TD="width: 108"][/TD]
[TD="width: 115"]

0000-00980555

[/TD]
[/TR]
[TR]
[TD="width: 28"]

7

[/TD]
[TD="width: 113"]

19/08/2019

[/TD]
[TD="width: 108"][/TD]
[TD="width: 115"]

0000-00980650

[/TD]
[/TR]
[TR]
[TD="width: 28"]

8

[/TD]
[TD="width: 113"]

21/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00981675

[/TD]
[/TR]
[TR]
[TD="width: 28"]

9

[/TD]
[TD="width: 113"]

21/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00982714

[/TD]
[/TR]
[TR]
[TD="width: 28"]

10

[/TD]
[TD="width: 113"]

21/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00983125

[/TD]
[/TR]
</tbody>[/TABLE]

Expected result

 [TABLE="class: MsoNormalTable, width: 219"]
<tbody>[TR]
[TD="width: 28"][/TD]
[TD="width: 113"]

A

[/TD]
[TD="width: 108"]

B

[/TD]
[TD="width: 115"]

C

[/TD]
[/TR]
[TR]
[TD="width: 28"]

4

[/TD]
[TD="width: 113"]

Date

[/TD]
[TD="width: 108"]

F & B Department

[/TD]
[TD="width: 115"]

Requisition #

[/TD]
[/TR]
[TR]
[TD="width: 28"]

5

[/TD]
[TD="width: 113"]

19/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00975108

[/TD]
[/TR]
[TR]
[TD="width: 28"]

6

[/TD]
[TD="width: 113"]

19/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00980555

[/TD]
[/TR]
[TR]
[TD="width: 28"]

7

[/TD]
[TD="width: 113"]

19/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00980650

[/TD]
[/TR]
[TR]
[TD="width: 28"]

8

[/TD]
[TD="width: 113"]

21/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00981675

[/TD]
[/TR]
[TR]
[TD="width: 28"]

9

[/TD]
[TD="width: 113"]

21/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00982714

[/TD]
[/TR]
[TR]
[TD="width: 28"]

10

[/TD]
[TD="width: 113"]

21/08/2019

[/TD]
[TD="width: 108"]

0002

[/TD]
[TD="width: 115"]

0000-00983125

[/TD]
[/TR]
</tbody>[/TABLE]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]Date[/td][td]F & B Department[/td][td]Requisition #[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

19/08/2019

[/td][td][/td][td]TEXT1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

19/08/2019

[/td][td][/td][td]TEXT2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

19/08/2019

[/td][td][/td][td]0000-00980650[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

21/08/2019

[/td][td]

0002

[/td][td]0000-00981675[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

21/08/2019

[/td][td]

0002

[/td][td]0000-00982714[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

21/08/2019

[/td][td]

0002

[/td][td]0000-00983125[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

22/08/2019

[/td][td]

0003

[/td][td]0000-00983126[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

23/08/2019

[/td][td]

0004

[/td][td]0000-00983127[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]

24/08/2019

[/td][td][/td][td]0000-00983128[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]

25/08/2019

[/td][td]

0006

[/td][td]0000-00983129[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15

[/td][td]

26/08/2019

[/td][td][/td][td]0000-00983130[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16

[/td][td]

27/08/2019

[/td][td][/td][td]0000-00983131[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17

[/td][td]

28/08/2019

[/td][td][/td][td]0000-00983132[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18

[/td][td]

29/08/2019

[/td][td]

0010

[/td][td]0000-00983133[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1 (2)[/td][/tr][/table]

RESULT

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]Date[/td][td]F & B Department[/td][td]Requisition #[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

19/08/2019

[/td][td]

0002

[/td][td]0002-TEXT1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

19/08/2019

[/td][td]

0002

[/td][td]0002-TEXT2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

19/08/2019

[/td][td]

0002

[/td][td]0002-00980650[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

21/08/2019

[/td][td]

0002

[/td][td]0002-00981675[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

21/08/2019

[/td][td]

0002

[/td][td]0002-00982714[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

21/08/2019

[/td][td]

0002

[/td][td]0002-00983125[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

22/08/2019

[/td][td]

0003

[/td][td]0003-00983126[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

23/08/2019

[/td][td]

0004

[/td][td]0004-00983127[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]

24/08/2019

[/td][td]

0006

[/td][td]0006-00983128[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]

25/08/2019

[/td][td]

0006

[/td][td]0006-00983129[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15

[/td][td]

26/08/2019

[/td][td]

0010

[/td][td]0010-00983130[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16

[/td][td]

27/08/2019

[/td][td]

0010

[/td][td]0010-00983131[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17

[/td][td]

28/08/2019

[/td][td]

0010

[/td][td]0010-00983132[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18

[/td][td]

29/08/2019

[/td][td]

0010

[/td][td]0010-00983133[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1 (2)[/td][/tr][/table]

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