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,"")

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" wrote:

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,"")

it's manually input...

"Atishoo" wrote:

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" wrote:

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,"")

I think the best way will be to use the visual basic editor. alt f11
click on the worksheet in question (in this example sheet1) and select
worksheet in the left drop dowm box.
Paste the following in there

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Worksheets("sheet1")
If .Range("B4") = "sold" Then
.Range("C4") = .Range("A1").Value
.Range("B4") = "sold " & Format(Now(), "mm/dd/yyyy") & " " & "at"
End If
End With
End Sub

Im guessing that C4 would contain the formula you posted in your question.

this example will only work for the single cell B4, I am also guessing that
you have a whole column of sold and in stock entries that you would wish this
to apply to.
in this case use the following instead:

With Worksheets("Sheet1").Range("B4:B1000")
Dim c As Range
Set c = .Find("sold", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = "sold " & Format(Now(), "mm/dd/yyyy") & " " & "at"
c.Offset(0, 1).Value = .Range("A1").Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress

End If
End With
End Sub

"Tufail" wrote:

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,"")

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

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

"Tufail" wrote:

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,"")