HI Guys
Just finished a post re formulas now need some VBA help/advice.
VBA Query
Now for Phase 2 with all the formulas working I now need advice/help with columns X & Y
My current situation is I only have column Y on my live sheet. I have some VBA done a few years ago by a helpful forum member. (embeded in the copy sheet attached)
In essence and without boring you to death prices change daily so I needed a solution to fix the prices after a sale.
As live data changes hourly I didn’t want my sales figures to keep changing.
So when I select Y (Yes) in column Y it should fix the values in the following cells.
H,J,K,Q and S this would lock down the sale
VBA supplied**
So I need to make sure the code is doing this.
But I also need an option to have a refund which could be in column X OR added to the dropdown in column Y as Y = Fix Sale R=Refund
The Refund needs to set columns H J F to £0.00 this should leave Profit showing £-0.20 as PayPal still charge the seller even after a refund.
It would be better to build it into column Y and rename it Status.
If this needs a new post please let me know I will recreate it in another forum topic.
Also thank you for all your help so far as you can see I have done a lot myself but a few areas do not click with me still in Excel
**Private Sub Worksheet_Change(ByVal Target As Range)
'dont do anything if multi cells selected or cell is not column V or is not input as "Y"
If Target.Cells.Count > 1 Then Exit Sub
If Not Target.Column = 22 Then Exit Sub '22 = column V
If Not UCase(Target) = "Y" Then Exit Sub
'dont do anything if Profit shows an error ie info is incomplete
If Evaluate("ISERROR(" & Target.Offset(0, -1).Address & ")") Then
MsgBox "Line details are incomplete. Please check and try again!", vbOKOnly, "Sorry"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
Exit Sub
End If
'Otherwise....
If MsgBox("Please confirm that you wish to fix this entry.", vbYesNo, "Confirm Details?") = vbNo Then Exit Sub
'if is the last row of table then add a dummy row to maintain the formulas
r = Target.Row
lr = Cells(Rows.Count, "A").End(xlUp).Row
If r = lr Then Cells(lr + 1, "A") = "NEXT ITEM"
'change the row's formula results to hard value
Range("A" & r & ":S" & r).Value = Range("A" & r & ":S" & r).Value
End Sub
Bookmarks