# macro for updating cells



## instanceoftime (Dec 22, 2022)

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)

20COKE CLASSIC 24/12 OZ5.9912/15/2214SUNDRIESBeverages20COKE CLASSIC 24/12 OZ5.9911/4/2214SUNDRIES04900001278111.49Beverages20COKE CLASSIC 24/12 OZ12.694/13/2214SUNDRIES04900001278111.4925COKE DIET 24/12 OZ14.4911/4/2214SUNDRIESBeverages25COKE DIET 24/12 OZ13.398/11/2214SUNDRIESBeverages25COKE DIET 24/12 OZ12.696/23/2214SUNDRIES04900001063311.49Beverages25COKE DIET 24/12 OZ11.697/27/2114SUNDRIES04900001063311.4925COKE DIET 24/12 OZ11.195/26/2114SUNDRIES04900001063311.4925COKE DIET 24/12 OZ10.991/13/2114SUNDRIES04900001063311.4981PEPSI 24/12Z HI-CONE P1008.492/18/2214SUNDRIES012017017.6981PEPSI 24/12Z HI-CONE P1006.595/5/2114SUNDRIES012017017.6983DIET PEPSI 24/12Z HI-CONE8.498/27/2214SUNDRIESBeverages83DIET PEPSI 24/12Z HI-CONE8.495/31/2214SUNDRIES0120001718577.69Beverages83DIET PEPSI 24/12Z HI-CONE6.595/5/2114SUNDRIES0120001718577.69123RUBBER MULCH 1.5CUFT RED10.999/14/2227GARDENGardening150KIKKOMAN SOY SAUCE 64 OZ6.6911/4/2213FOODSauces150KIKKOMAN SOY SAUCE 64 OZ5.9910/17/2213FOODSauces150KIKKOMAN SOY SAUCE 64 OZ5.9910/6/2213FOOD0413900015055.39Sauces


----------



## HongRu (Dec 22, 2022)

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 (Dec 22, 2022)

HongRu said:


> try this.
> 
> ```
> Sub SbFillBlank()
> ...


----------



## Peter_SSs (Dec 22, 2022)

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 (Dec 22, 2022)

HongRu said:


> try this.
> 
> ```
> Sub SbFillBlank()
> ...


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 (Dec 22, 2022)

Peter_SSs said:


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


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


----------



## Peter_SSs (Dec 22, 2022)

instanceoftime said:


> 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.xlsmABCDEFGHI120COKE CLASSIC 24/12 OZ5.994491014SUNDRIESBeverages220COKE CLASSIC 24/12 OZ5.994486914SUNDRIES4900001278111.49Beverages320COKE CLASSIC 24/12 OZ12.694466414SUNDRIES4900001278111.49425COKE DIET 24/12 OZ14.494486914SUNDRIESBeverages525COKE DIET 24/12 OZ13.394478414SUNDRIESBeverages625COKE DIET 24/12 OZ12.694473514SUNDRIES4900001063311.49Beverages725COKE DIET 24/12 OZ11.694440414SUNDRIES4900001063311.49825COKE DIET 24/12 OZ11.194434214SUNDRIES4900001063311.49925COKE DIET 24/12 OZ10.994420914SUNDRIES4900001063311.491081PEPSI 24/12Z HI-CONE P1008.494461014SUNDRIES12017017.691181PEPSI 24/12Z HI-CONE P1006.594432114SUNDRIES12017017.691283DIET PEPSI 24/12Z HI-CONE8.494480014SUNDRIESBeverages1383DIET PEPSI 24/12Z HI-CONE8.494471214SUNDRIES120001718577.69Beverages1483DIET PEPSI 24/12Z HI-CONE6.594432114SUNDRIES120001718577.6915123RUBBER MULCH 1.5CUFT RED10.994481827GARDENGardening16150KIKKOMAN SOY SAUCE 64 OZ6.694486913FOODSauces17150KIKKOMAN SOY SAUCE 64 OZ5.994485113FOODSauces18150KIKKOMAN SOY SAUCE 64 OZ5.994484013FOOD413900015055.39SaucesSheet1

.. and after the code

instanceoftime.xlsmABCDEFGHI120COKE CLASSIC 24/12 OZ5.994491014SUNDRIES4900001278111.49Beverages220COKE CLASSIC 24/12 OZ5.994486914SUNDRIES4900001278111.49Beverages320COKE CLASSIC 24/12 OZ12.694466414SUNDRIES4900001278111.49425COKE DIET 24/12 OZ14.494486914SUNDRIES4900001063311.49Beverages525COKE DIET 24/12 OZ13.394478414SUNDRIES4900001063311.49Beverages625COKE DIET 24/12 OZ12.694473514SUNDRIES4900001063311.49Beverages725COKE DIET 24/12 OZ11.694440414SUNDRIES4900001063311.49825COKE DIET 24/12 OZ11.194434214SUNDRIES4900001063311.49925COKE DIET 24/12 OZ10.994420914SUNDRIES4900001063311.491081PEPSI 24/12Z HI-CONE P1008.494461014SUNDRIES12017017.691181PEPSI 24/12Z HI-CONE P1006.594432114SUNDRIES12017017.691283DIET PEPSI 24/12Z HI-CONE8.494480014SUNDRIES120001718577.69Beverages1383DIET PEPSI 24/12Z HI-CONE8.494471214SUNDRIES120001718577.69Beverages1483DIET PEPSI 24/12Z HI-CONE6.594432114SUNDRIES120001718577.6915123RUBBER MULCH 1.5CUFT RED10.994481827GARDENGardening16150KIKKOMAN SOY SAUCE 64 OZ6.694486913FOOD413900015055.39Sauces17150KIKKOMAN SOY SAUCE 64 OZ5.994485113FOOD413900015055.39Sauces18150KIKKOMAN SOY SAUCE 64 OZ5.994484013FOOD413900015055.39SaucesSheet1

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 (Dec 22, 2022)

Peter_SSs said:


> 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 (Dec 23, 2022)

HongRu said:


> try this.
> 
> ```
> Sub SbFillBlank()
> ...


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 (Dec 23, 2022)

instanceoftime said:


> this worked perfectly on my sample.


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


----------

