VBA to update selected column values combining the current value of the selected column with the value in the column next to it

[gberg]

I have a table that has two columns. One column for quantity for the current order the second for quantities for previously ordered items

I want a macro that adds the two together and then updates the Prev Ordered column

It would start out like this

And end up like

If possible, I would like to filter by only the items that have a value in the Ordered Qty so there are not as many calculations to go through. Some tables could have thousands of rows but only have a handful of items to be ordered.

Thanks

Greg

 

[Colo]

Hi Greg,
Still haven't tried to filter the data but give this simple one a try first.
Change table name to suit.

Sub Sample()
    Dim c As Range
    Set c = Selection
    Application.ScreenUpdating = False
    Range("Table1[Order Qty]").Copy
    Range("Table1[Prev Ordered]").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
    Range("Table1[Order Qty]").ClearContents
    c.Select
    Application.ScreenUpdating = True
End Sub

 

[nemmi69]

So I assumed the current order was in A and previous in B

Sub OrdersUpdate()
Dim WbName As Workbook
Dim WsName1 As Worksheet
Dim CurOrder As Range
Dim PrevOrdr As Range
Dim LastRowOrders As Long
Dim OLoop As Long

'Sets the name of the workbook and sheet
Set WbName = ThisWorkbook
Windows(ThisWorkbook.Name).Activate
Set WsName1 = WbName.Sheets(1)

'Finds last row
LastRowOrders = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Assume headers are in row 1
'Assume Current order is in column A and previous in B

For OLoop = 2 To LastRowOrders
    If WsName1.Range("A" & OLoop).Value > 0 Then
        WsName1.Range("B" & OLoop).Value = WsName1.Range("B" & OLoop).Value + WsName1.Range("A" & OLoop).Value
        'If you want the Current order deleted uncomment the row below
        'WsName1.Range("A" & OLoop).Clear
    End If
Next OLoop

End Sub

Result

 

[gberg]

Colo that worked great, but not if you have filters set. Without filters it worked perfectly. With filters the results were not good. It seems to run quickly so I will be fine just making sure there are no filters and all good. When the data was filtered the results showed up in incorrect corresponding cells and it was put into multiple cells. Again, I will just make sure the table is not filtered.

 

[nemmi69]

Yes, it wouldn't handle a filter

 

[Alex Blakenburg]

If you want to after turning off screen updating Application.ScreenUpdating = False you could add this:

If ActiveSheet.FilterMode = True Then
    ActiveSheet.ShowAllData 
End If

 

[gberg]

Perfect!