P
Pablo
With a lot of help from Bart I created a macro that deletes a row from a
Shopping Cart worksheet and removes the quantity from a Catalog worksheet.
The problem is that I need to protect the top half, rows 1-24, of the
Shopping Cart. I am thinking I can probably consolidate these two sub
programs and put them between an If statement, but I am not familiar enough
with ranges.
Sub DeleteFromCart_Single()
Dim i As Long
Dim lRow As Long
Dim LRCart As Long
Dim arrItem
Dim arrInCart
'get the item to update
lRow = ActiveCell.Row
arrItem = Range(Cells(lRow, 1), Cells(lRow, 7))
'QTY, SKU, ISBN, Level, Description, List Price, Institution Price,
Extended Price
'---------------------------------------------------
With Sheets("Catalog")
LRCart = .Cells(65536, 1).End(xlUp).Row
arrInCart = Range(.Cells(2, 1), .Cells(LRCart, 7))
For i = 1 To UBound(arrInCart)
If arrItem(1, 2) = arrInCart(i, 2) Or _
arrItem(1, 3) = arrInCart(i, 3) Then
'update existing cart item
.Cells(i + 1, 1) = "" 'QTY
'.Cells(i + 1, 7) = arrItem(1, 7) 'Price
'.Cells(i + 1, 8) = arrItem(1, 8) 'Extended Price
DeleteRow
Exit Sub
End If
Next i
'update catalog item
Range(.Cells(LRCart + 1, 1), .Cells(LRCart + 1, 7)) = arrItem
End With
End Sub
Sub DeleteRow()
'ActiveSheet.Unprotect ("adulted")
ActiveCell.EntireRow.Delete
'ActiveSheet.Protect ("adulted")
End Sub
Shopping Cart worksheet and removes the quantity from a Catalog worksheet.
The problem is that I need to protect the top half, rows 1-24, of the
Shopping Cart. I am thinking I can probably consolidate these two sub
programs and put them between an If statement, but I am not familiar enough
with ranges.
Sub DeleteFromCart_Single()
Dim i As Long
Dim lRow As Long
Dim LRCart As Long
Dim arrItem
Dim arrInCart
'get the item to update
lRow = ActiveCell.Row
arrItem = Range(Cells(lRow, 1), Cells(lRow, 7))
'QTY, SKU, ISBN, Level, Description, List Price, Institution Price,
Extended Price
'---------------------------------------------------
With Sheets("Catalog")
LRCart = .Cells(65536, 1).End(xlUp).Row
arrInCart = Range(.Cells(2, 1), .Cells(LRCart, 7))
For i = 1 To UBound(arrInCart)
If arrItem(1, 2) = arrInCart(i, 2) Or _
arrItem(1, 3) = arrInCart(i, 3) Then
'update existing cart item
.Cells(i + 1, 1) = "" 'QTY
'.Cells(i + 1, 7) = arrItem(1, 7) 'Price
'.Cells(i + 1, 8) = arrItem(1, 8) 'Extended Price
DeleteRow
Exit Sub
End If
Next i
'update catalog item
Range(.Cells(LRCart + 1, 1), .Cells(LRCart + 1, 7)) = arrItem
End With
End Sub
Sub DeleteRow()
'ActiveSheet.Unprotect ("adulted")
ActiveCell.EntireRow.Delete
'ActiveSheet.Protect ("adulted")
End Sub