Repeat filling QTY into the empty cells of adjacent cells based on the previous column

[tubrak]

Hi,

every day I add new column contains DATE(TODAY) and will add some values in cells and some cells will be empty .

what I want filling the same QTY in the empty cell under date (today) header based on adjecnt cells for precede column .

here is data

TT.xlsm
	A	B	C	D	E	F	G	H
1	S.N	ITEM	QTY	13/01/2024	14/01/2024	15/01/2024	16/01/2024	17/01/2024
2	1	CTR 12-200M45 MM 123H K/L	12.00	22.00
3	2	CCTR 12-200M45 MM 123H K/L	13.00
4	3	TR 200M45MM LK H K/L	14.00
5	4	TR 200M451 NMM LK H K/L	15.00
6	5	TTR 12200S45 MN 123	16.00
7	6	TR 1425/148V MN 123H K/L	17.00
8	7	TR 200M45 MM 123H K/L SS	18.00	12.00
9	8	TR 2.5M100* 123H K/L	19.00
10	9	TR 2.5M100**123H K/L	20.00
11	10	TRMN 22.5M100* 123H K/L	21.00
12	11	TTRM 12200S45 MN 1234	22.00
13	12	TTRR 1200S45 MN 123	23.00
14	13	TR 1200M45MM LK H K/L	24.00
15	14	BTR-100***8 RRT-DE34230	25.00	11.00
16	15	BTR-100***8 RRT-DE34231	26.00
17	16	BTR-100***8 RRT-DE34232	27.00
18	17	BTR-100***8 RRT-DE34233	28.00
19	18	BTR-100***8 RRT-DE34234	29.00
20	19	TR 2.5M100**123H K/LM	30.00
ITEMS

result

TT.xlsm
	A	B	C	D	E	F	G	H
1	S.N	ITEM	QTY	13/01/2024	14/01/2024	15/01/2024	16/01/2024	17/01/2024
2	1	CTR 12-200M45 MM 123H K/L	12.00	22.00
3	2	CCTR 12-200M45 MM 123H K/L	13.00	13.00
4	3	TR 200M45MM LK H K/L	14.00	14.00
5	4	TR 200M451 NMM LK H K/L	15.00	15.00
6	5	TTR 12200S45 MN 123	16.00	16.00
7	6	TR 1425/148V MN 123H K/L	17.00	17.00
8	7	TR 200M45 MM 123H K/L SS	18.00	12.00
9	8	TR 2.5M100* 123H K/L	19.00	19.00
10	9	TR 2.5M100**123H K/L	20.00	20.00
11	10	TRMN 22.5M100* 123H K/L	21.00	21.00
12	11	TTRM 12200S45 MN 1234	22.00	22.00
13	12	TTRR 1200S45 MN 123	23.00	23.00
14	13	TR 1200M45MM LK H K/L	24.00	24.00
15	14	BTR-100***8 RRT-DE34230	25.00	11.00
16	15	BTR-100***8 RRT-DE34231	26.00	26.00
17	16	BTR-100***8 RRT-DE34232	27.00	27.00
18	17	BTR-100***8 RRT-DE34233	28.00	28.00
19	18	BTR-100***8 RRT-DE34234	29.00	29.00
20	19	TR 2.5M100**123H K/LM	30.00	30.00
ITEMS

another example

TT.xlsm
	A	B	C	D	E	F	G	H
1	S.N	ITEM	QTY	13/01/2024	14/01/2024	15/01/2024	16/01/2024	17/01/2024
2	1	CTR 12-200M45 MM 123H K/L	12.00	22.00
3	2	CCTR 12-200M45 MM 123H K/L	13.00	13.00
4	3	TR 200M45MM LK H K/L	14.00	14.00	122.00
5	4	TR 200M451 NMM LK H K/L	15.00	15.00
6	5	TTR 12200S45 MN 123	16.00	16.00
7	6	TR 1425/148V MN 123H K/L	17.00	17.00	12.00
8	7	TR 200M45 MM 123H K/L SS	18.00	12.00
9	8	TR 2.5M100* 123H K/L	19.00	19.00
10	9	TR 2.5M100**123H K/L	20.00	20.00	34.00
11	10	TRMN 22.5M100* 123H K/L	21.00	21.00
12	11	TTRM 12200S45 MN 1234	22.00	22.00
13	12	TTRR 1200S45 MN 123	23.00	23.00
14	13	TR 1200M45MM LK H K/L	24.00	24.00	34.00
15	14	BTR-100***8 RRT-DE34230	25.00	11.00
16	15	BTR-100***8 RRT-DE34231	26.00	26.00
17	16	BTR-100***8 RRT-DE34232	27.00	27.00	45.00
18	17	BTR-100***8 RRT-DE34233	28.00	28.00
19	18	BTR-100***8 RRT-DE34234	29.00	29.00
20	19	TR 2.5M100**123H K/LM	30.00	30.00
ITEMS

result

TT.xlsm
	A	B	C	D	E	F	G	H
1	S.N	ITEM	QTY	13/01/2024	14/01/2024	15/01/2024	16/01/2024	17/01/2024
2	1	CTR 12-200M45 MM 123H K/L	12.00	22.00	22.00
3	2	CCTR 12-200M45 MM 123H K/L	13.00	13.00	13.00
4	3	TR 200M45MM LK H K/L	14.00	14.00	122.00
5	4	TR 200M451 NMM LK H K/L	15.00	15.00	15.00
6	5	TTR 12200S45 MN 123	16.00	16.00	16.00
7	6	TR 1425/148V MN 123H K/L	17.00	17.00	12.00
8	7	TR 200M45 MM 123H K/L SS	18.00	12.00	12.00
9	8	TR 2.5M100* 123H K/L	19.00	19.00	19.00
10	9	TR 2.5M100**123H K/L	20.00	20.00	34.00
11	10	TRMN 22.5M100* 123H K/L	21.00	21.00	21.00
12	11	TTRM 12200S45 MN 1234	22.00	22.00	22.00
13	12	TTRR 1200S45 MN 123	23.00	23.00	23.00
14	13	TR 1200M45MM LK H K/L	24.00	24.00	34.00
15	14	BTR-100***8 RRT-DE34230	25.00	11.00	11.00
16	15	BTR-100***8 RRT-DE34231	26.00	26.00	26.00
17	16	BTR-100***8 RRT-DE34232	27.00	27.00	45.00
18	17	BTR-100***8 RRT-DE34233	28.00	28.00	28.00
19	18	BTR-100***8 RRT-DE34234	29.00	29.00	29.00
20	19	TR 2.5M100**123H K/LM	30.00	30.00	30.00
ITEMS

I want macro for 5000 rows if it's possible
thank you

 

[DanteAmor]

Try this:

Sub filling_qty()
  Dim j As Long, lr As Long
  Application.ScreenUpdating = False
  
  lr = Range("B" & Rows.Count).End(3).Row
  For j = 4 To Cells(1, Columns.Count).End(1).Column
    If Cells(1, j).Value = Date Then
      With Range(Cells(2, j), Cells(lr, j))
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
      End With
      Exit For
    End If
  Next

  Application.ScreenUpdating = True
  Application.CutCopyMode = False
End Sub

 

[Kerryx]

follow the below

How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)

To fill blank cells with value from the left in Excel, we have shown four different methods, including a VBA code.

www.exceldemy.com

 

[tubrak]

Hi Dante again !
the code is really efficient .
how can I fix the error in this line

        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"

if there is no empty cell ,please?

 

[tubrak]

thanks Kerryx I will read the article .

​

 

[DanteAmor]

if there is no empty cell ,please?

Try:

Sub filling_qty()
  Dim j As Long, lr As Long
  Application.ScreenUpdating = False
  
  lr = Range("B" & Rows.Count).End(3).Row
  For j = 4 To Cells(1, Columns.Count).End(1).Column
    If Cells(1, j).Value = Date Then
      With Range(Cells(2, j), Cells(lr, j))
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
      End With
      Exit For
    End If
  Next

  Application.ScreenUpdating = True
  Application.CutCopyMode = False
End Sub

 

[tubrak]

thanks for your assistance .