macro for updating cells

[instanceoftime]

Thank you all for past help, I am here yet again.

I have a spreadsheet of inventory. I paste into it incoming inventory and would like to copy current info (price and sku) into the new cells.

The spreadsheet is sorted so any new inventory shows first.

If the item number is the same (A) I would like to copy (G) and (H) from below it (directly or not directly if it is blank as well)

20	COKE CLASSIC 24/12 OZ	5.99	12/15/22	14	SUNDRIES			Beverages
20	COKE CLASSIC 24/12 OZ	5.99	11/4/22	14	SUNDRIES	049000012781	11.49	Beverages
20	COKE CLASSIC 24/12 OZ	12.69	4/13/22	14	SUNDRIES	049000012781	11.49
25	COKE DIET 24/12 OZ	14.49	11/4/22	14	SUNDRIES			Beverages
25	COKE DIET 24/12 OZ	13.39	8/11/22	14	SUNDRIES			Beverages
25	COKE DIET 24/12 OZ	12.69	6/23/22	14	SUNDRIES	049000010633	11.49	Beverages
25	COKE DIET 24/12 OZ	11.69	7/27/21	14	SUNDRIES	049000010633	11.49
25	COKE DIET 24/12 OZ	11.19	5/26/21	14	SUNDRIES	049000010633	11.49
25	COKE DIET 24/12 OZ	10.99	1/13/21	14	SUNDRIES	049000010633	11.49
81	PEPSI 24/12Z HI-CONE P100	8.49	2/18/22	14	SUNDRIES	01201701	7.69
81	PEPSI 24/12Z HI-CONE P100	6.59	5/5/21	14	SUNDRIES	01201701	7.69
83	DIET PEPSI 24/12Z HI-CONE	8.49	8/27/22	14	SUNDRIES			Beverages
83	DIET PEPSI 24/12Z HI-CONE	8.49	5/31/22	14	SUNDRIES	012000171857	7.69	Beverages
83	DIET PEPSI 24/12Z HI-CONE	6.59	5/5/21	14	SUNDRIES	012000171857	7.69
123	RUBBER MULCH 1.5CUFT RED	10.99	9/14/22	27	GARDEN			Gardening
150	KIKKOMAN SOY SAUCE 64 OZ	6.69	11/4/22	13	FOOD			Sauces
150	KIKKOMAN SOY SAUCE 64 OZ	5.99	10/17/22	13	FOOD			Sauces
150	KIKKOMAN SOY SAUCE 64 OZ	5.99	10/6/22	13	FOOD	041390001505	5.39	Sauces

 

[HongRu]

try this.

Sub SbFillBlank()
    Dim R1 As Range
    Set R1 = Range("A1") 'set the data's first cell
    
    Dim myRows
    myRows = R1.CurrentRegion.Rows.Count
    
    Dim i
    For i = myRows - 1 To 0 Step -1
        If R1.Offset(i, 6) = "" And R1.Offset(i, 0) = R1.Offset(i + 1, 0) Then
            R1.Offset(i, 6) = R1.Offset(i + 1, 6)
            R1.Offset(i, 7) = R1.Offset(i + 1, 7)
        End If
    Next i
End Sub

 

[instanceoftime]

try this.

Sub SbFillBlank()
    Dim R1 As Range
    Set R1 = Range("A1") 'set the data's first cell
   
    Dim myRows
    myRows = R1.CurrentRegion.Rows.Count
   
    Dim i
    For i = myRows - 1 To 0 Step -1
        If R1.Offset(i, 6) = "" And R1.Offset(i, 0) = R1.Offset(i + 1, 0) Then
            R1.Offset(i, 6) = R1.Offset(i + 1, 6)
            R1.Offset(i, 7) = R1.Offset(i + 1, 7)
        End If
    Next i
End Sub

 

[Peter_SSs]

If I have understood correctly, this should do them all at once.

Sub Fill_Values()
  With Range("G1:H" & Range("A" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=IF(RC1=R[1]C1,R[1]C,"""")"
    .Value = .Value
  End With
End Sub

 

[instanceoftime]

try this.

Sub SbFillBlank()
    Dim R1 As Range
    Set R1 = Range("A1") 'set the data's first cell
   
    Dim myRows
    myRows = R1.CurrentRegion.Rows.Count
   
    Dim i
    For i = myRows - 1 To 0 Step -1
        If R1.Offset(i, 6) = "" And R1.Offset(i, 0) = R1.Offset(i + 1, 0) Then
            R1.Offset(i, 6) = R1.Offset(i + 1, 6)
            R1.Offset(i, 7) = R1.Offset(i + 1, 7)
        End If
    Next i
End Sub

I didn't get any output?

also I was hoping to start with row 1 and go throught last row on sheet. (currently 30k row)

 

[instanceoftime]

If I have understood correctly, this should do them all at once.

Sub Fill_Values()
  With Range("G1:H" & Range("A" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=IF(RC1=R[1]C1,R[1]C,"""")"
    .Value = .Value
  End With
End Sub

Hmm, didn't do anything for me? I will have to look again to make sure it's not me.

 

[Peter_SSs]

Hmm, didn't do anything for me? I will have to look again to make sure it's not me.

Are you sure that you have given us the correct column information?
Here is my sheet before the code was run

instanceoftime.xlsm
	A	B	C	D	E	F	G	H	I
1	20	COKE CLASSIC 24/12 OZ	5.99	44910	14	SUNDRIES			Beverages
2	20	COKE CLASSIC 24/12 OZ	5.99	44869	14	SUNDRIES	49000012781	11.49	Beverages
3	20	COKE CLASSIC 24/12 OZ	12.69	44664	14	SUNDRIES	49000012781	11.49
4	25	COKE DIET 24/12 OZ	14.49	44869	14	SUNDRIES			Beverages
5	25	COKE DIET 24/12 OZ	13.39	44784	14	SUNDRIES			Beverages
6	25	COKE DIET 24/12 OZ	12.69	44735	14	SUNDRIES	49000010633	11.49	Beverages
7	25	COKE DIET 24/12 OZ	11.69	44404	14	SUNDRIES	49000010633	11.49
8	25	COKE DIET 24/12 OZ	11.19	44342	14	SUNDRIES	49000010633	11.49
9	25	COKE DIET 24/12 OZ	10.99	44209	14	SUNDRIES	49000010633	11.49
10	81	PEPSI 24/12Z HI-CONE P100	8.49	44610	14	SUNDRIES	1201701	7.69
11	81	PEPSI 24/12Z HI-CONE P100	6.59	44321	14	SUNDRIES	1201701	7.69
12	83	DIET PEPSI 24/12Z HI-CONE	8.49	44800	14	SUNDRIES			Beverages
13	83	DIET PEPSI 24/12Z HI-CONE	8.49	44712	14	SUNDRIES	12000171857	7.69	Beverages
14	83	DIET PEPSI 24/12Z HI-CONE	6.59	44321	14	SUNDRIES	12000171857	7.69
15	123	RUBBER MULCH 1.5CUFT RED	10.99	44818	27	GARDEN			Gardening
16	150	KIKKOMAN SOY SAUCE 64 OZ	6.69	44869	13	FOOD			Sauces
17	150	KIKKOMAN SOY SAUCE 64 OZ	5.99	44851	13	FOOD			Sauces
18	150	KIKKOMAN SOY SAUCE 64 OZ	5.99	44840	13	FOOD	41390001505	5.39	Sauces
Sheet1

.. and after the code

instanceoftime.xlsm
	A	B	C	D	E	F	G	H	I
1	20	COKE CLASSIC 24/12 OZ	5.99	44910	14	SUNDRIES	49000012781	11.49	Beverages
2	20	COKE CLASSIC 24/12 OZ	5.99	44869	14	SUNDRIES	49000012781	11.49	Beverages
3	20	COKE CLASSIC 24/12 OZ	12.69	44664	14	SUNDRIES	49000012781	11.49
4	25	COKE DIET 24/12 OZ	14.49	44869	14	SUNDRIES	49000010633	11.49	Beverages
5	25	COKE DIET 24/12 OZ	13.39	44784	14	SUNDRIES	49000010633	11.49	Beverages
6	25	COKE DIET 24/12 OZ	12.69	44735	14	SUNDRIES	49000010633	11.49	Beverages
7	25	COKE DIET 24/12 OZ	11.69	44404	14	SUNDRIES	49000010633	11.49
8	25	COKE DIET 24/12 OZ	11.19	44342	14	SUNDRIES	49000010633	11.49
9	25	COKE DIET 24/12 OZ	10.99	44209	14	SUNDRIES	49000010633	11.49
10	81	PEPSI 24/12Z HI-CONE P100	8.49	44610	14	SUNDRIES	1201701	7.69
11	81	PEPSI 24/12Z HI-CONE P100	6.59	44321	14	SUNDRIES	1201701	7.69
12	83	DIET PEPSI 24/12Z HI-CONE	8.49	44800	14	SUNDRIES	12000171857	7.69	Beverages
13	83	DIET PEPSI 24/12Z HI-CONE	8.49	44712	14	SUNDRIES	12000171857	7.69	Beverages
14	83	DIET PEPSI 24/12Z HI-CONE	6.59	44321	14	SUNDRIES	12000171857	7.69
15	123	RUBBER MULCH 1.5CUFT RED	10.99	44818	27	GARDEN			Gardening
16	150	KIKKOMAN SOY SAUCE 64 OZ	6.69	44869	13	FOOD	41390001505	5.39	Sauces
17	150	KIKKOMAN SOY SAUCE 64 OZ	5.99	44851	13	FOOD	41390001505	5.39	Sauces
18	150	KIKKOMAN SOY SAUCE 64 OZ	5.99	44840	13	FOOD	41390001505	5.39	Sauces
Sheet1

The post #2 code also worked for me, (but it loops individually through each row, which would likely be quite slow for 30k rows)

 

[HongRu]

Are you sure that you have given us the correct column information?

I can't figure out why you get no output.
Maybe you can try this code amending from Peter_Sss's.
All you need is to make sure the Columns("G:H") is what you need.

Sub Fill_Values_1()
    Columns("G:H").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC1=R[1]C1,R[1]C,"""")"
    Columns("G:H") = Columns("G:H").Value
End Sub

 

[instanceoftime]

try this.

Sub SbFillBlank()
    Dim R1 As Range
    Set R1 = Range("A1") 'set the data's first cell
   
    Dim myRows
    myRows = R1.CurrentRegion.Rows.Count
   
    Dim i
    For i = myRows - 1 To 0 Step -1
        If R1.Offset(i, 6) = "" And R1.Offset(i, 0) = R1.Offset(i + 1, 0) Then
            R1.Offset(i, 6) = R1.Offset(i + 1, 6)
            R1.Offset(i, 7) = R1.Offset(i + 1, 7)
        End If
    Next i
End Sub

I don't know what I did yesterday but started fresh this morning and this worked perfectly on my sample. Thanks HongRu and others for your help.

 

[Peter_SSs]

this worked perfectly on my sample.

Just wondering how long it took to process 30,000 rows?
.. and did you compare the alternative?