Keeping Exchange Rate With Status

[Tufail]

hi,

my xchange rate is in A1 which is chang on daily basis, so i want if status
is in stock then want let change exr on daily basis, if status is changed to
sold then want keep previous rate.

A1 = Exchange rate (change on daily basis)
B4 = Status (change when product sold)
=IF($B4="Stock",$A$1,"")

 

[Atishoo]

hi
is the exchange rate in a1 updated manually by a user or is it a formula
that calculates exchange rate dependant on something else?

 

[Tufail]

it's manually input...

hi
is the exchange rate in a1 updated manually by a user or is it a formula
that calculates exchange rate dependant on something else?

 

[Atishoo]

in fact seeing as how it changes the value instantly it doesnt need to be
looped.

With Worksheets("Sheet1").Range("B4:B1000")
Dim c As Range
Set c = .Find("sold", LookIn:=xlValues)

If Not c Is Nothing Then

c.Value = "Sold " & Format(Now(), "mm/dd/yyyy") & " " & "at"
c.Offset(0, 1).Value = Range("A1").Value

End If
End With
End Sub

 

[Atishoo]

Sorry forgot to add in "look at xlwhole" this should work better.
I added in the date and "at" so that in column A it should state the product
in B sold (what ever todays date is) at then in column C the exchange rate
for that day.
Ground Almonds sold 09/12/09 at 1.2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Worksheets("Sheet1").Range("B4:B1000")
Dim c As Range
Set c = .Find("sold", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
c.Value = "Sold " & Format(Now(), "mm/dd/yyyy") & " " & "at"
c.Offset(0, 1).Value = Range("A1").Value
End If
End With
End Sub