Selected Answer
Hello leap,
There were only a few small changes needed. (John submitted his solution while I was working on mine)
Added column "E" with title "Old Price". Changed columns "C" & "E" number formats to "0.00; -0.00; -; @". Added code to check that TextBox1 is not empty and a check to see if the price in TextBox1 is a change from the existing price. Cells "D1" & "E1" formatted as General.
If there is a new price then: 1) enter the date in Col "D"; 2) enter the old price in Col "E"; 3) enter new price in Col "C". (revised file attached)
Private Sub CommandButton1_Click()
' revised by WillieD24, May 2024
Dim ws As Worksheet, LR As Long, qtyP As Double, qtyT As Double, f As Range
If ComboBox1.Value <> "" Then
Set ws = Sheets("PRICES")
LR = ws.Cells(Rows.Count, 2).End(3).Row
Set f = ws.Range("B:B").Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
With f.Offset(, 1) ' Column "C"
' check if there is a price in TextBox1
If TextBox1.Value = "" Then Exit Sub ' no price entered
' check if there is a change in the price
If f.Offset(, 1).Value = Val(TextBox1.Value) Then Exit Sub ' no change in price
' price in column "C" not equal to price in TextBox1
If .Value <> Val(TextBox1.Value) Then
' enter date in column "D"
f.Offset(, 2).Value = Date
' enter old price in column "E"
f.Offset(, 3).Value = f.Offset(, 1).Value
' enter new price in column "C"
f.Offset(, 1).Value = Val(TextBox1.Value)
End If
End With
End If
End Sub
Cheers :-)
Update May 29 @ 5 PM
Hi leap,
If I understand correctly, you want the date entered in cell "D1" and the new price in column "D". Doing it this way you won't know the date each price changed. You will only know the date when the last price change happened – but not which price was changed. I have made this change in "RF Rev2.xlsm" attached.
Private Sub CommandButton1_Click()
' revised by WillieD24, May 2024
Dim ws As Worksheet, LR As Long, qtyP As Double, qtyT As Double, f As Range
If ComboBox1.Value <> "" Then
Set ws = Sheets("PRICES")
LR = ws.Cells(Rows.Count, 2).End(3).Row
Set f = ws.Range("B:B").Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
With f.Offset(, 1) ' Column "C"
' check if there is a price in TextBox1
If TextBox1.Value = "" Then Exit Sub ' no price entered
' check if there is a change in the price
If f.Offset(, 1).Value = Val(TextBox1.Value) Then Exit Sub ' no change in price
' price in column "C" not equal to price in TextBox1
If .Value <> Val(TextBox1.Value) Then
' enter date in "D1"
Range("D1").Value = Date
' enter new price in column "D"
f.Offset(, 2).Value = Val(TextBox1.Value)
End If
End With
End If
End Sub
Cheers :-)
Update May 29 @ 8:45 PM
Hi leap,
In this 3rd revision, when a new price is entered, and "today's date" is different than that of the last column, a new column is added with today's date at the top and the new price entered into this new column. The result, a separate column for each date a new price(s) was entered. File "RF Rev3.xlsm" is attached.
Private Sub CommandButton1_Click()
' revised by WillieD24, May 2024
Dim ws As Worksheet, LR As Long, LC As Long, qtyP As Double, qtyT As Double, f As Range
If ComboBox1.Value <> "" Then
Set ws = Sheets("PRICES")
LR = ws.Cells(Rows.Count, 2).End(3).Row
LC = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set f = ws.Range("B:B").Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
With f.Offset(, 1) ' Column "C"
' check if there is a price in TextBox1
If TextBox1.Value = "" Then Exit Sub ' no price entered
' check if there is a change in the price
If f.Offset(, 1).Value = Val(TextBox1.Value) Then Exit Sub ' no change in price
' check if today's date is the same as date in last column
If Cells(1, LC).Value <> Date Then
' date is different so create a new column for the current date
' format next column
Columns(LC).Select
Selection.Copy
Columns(LC + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Cells(1, LC + 1) = Date ' enter current date for new column
Range(Cells(2, LC + 1), Cells(LR, LC + 1)).Value = "0"
Cells(1, LC + 1).Select
End If
' price in column "C" not equal to price in TextBox1
If f.Offset(, 1).Value <> Val(TextBox1.Value) And Cells(1, LC).Value <> Date Then
' enter new price in column of new date
f.Offset(, LC - 1).Value = Val(TextBox1.Value)
Else
' enter new price in column of current date
f.Offset(, LC - 2).Value = Val(TextBox1.Value)
End If
End With
End If
End Sub
Hope this is what you were looking for. If my answer solves your problem then please mark my answer as selected.
