IF function to fix values

[Mark2Aus]

I was just wondering if there is a way in the IF function to return a value if logic is true and not to change that value any longer.
Thus, I receive online in Cell A1, 20 different prices everyday and I would like to have all them recorded starting from cell B1 to B20.
When I write in every one of the cells, from B1 to B20 the same formula:
IF(ISNUMBER($A$1),$A$1,""), of course as A1 changes during the day, all of my B1 to B20 will change and show the same value as the last value in A1.
Is there a way to, after the first value is shown in A1, appearing also in B1, that it will not change any longer in B1 and only, when A1 changes again, B2 will then record that value, but not B1. And so on...
Any solution?
Thanks
Mark

 

[SkipVought]

Hi,

Use the Worksheet_Change event...

Private Sub Worksheet_Change(ByVal Target As Range)
   if not intersect(target, [A1]) is nothing then
      cells(cells.rows.count,"B").end(xlup).offset(1).value = [A1].value
   end if
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mark2Aus]

As this is the first time I am using a code (I am still learning VBA), is this the way I should do?
1) Open my workbook
2) Click Developer, then VBA
3) Select my Sheet
4) From the Insert Menu, click Module
5) Copy the code you wrote
6) Click Run
7) Give the macro a name
Is that OK?
Thanks

 

[SkipVought]

Give the macro a name? It already HAS a name.
[tt]
1) Open my workbook
2) Click Developer, then VBA
3) Select my Sheet
4) Right-Click the SHEET TAB
4a) Select View Code
5) Copy the code you wrote and paset in the code window.
6) Save
7) Return to sheet
[/tt]
Now enter a value in A1 and observe the results. This is an EVENT procedute, that fires when any value is CHANGED on that sheet. In the event that A1 is the cell whose value changed, that value gets put in column B. Of course when that happens, it's a change to the sheet, so the procedure fires AGAIN. However, since the chnaged value is not in A1 this time, nothing else happens.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mark2Aus]

Skip,
I've just tested the VBA code and it works just fine. Everytime there is a change in A1, the number is recorded down in column B. Perfect!
2 points of difference to the spreadsheet solution:
a) the VBA solution records every single change in A1, even zeros. Good.
b) it records in column B numbers that repeat themselves. Thus, if A1 changes to 60, 60 is recorded say in B9. Then, if A1 changes to the same 60 again, 60 is now recorded in B10, etc. Whilst the spreadsheet solution only records different numbers.
Of course this was my fault as I wasn't clear that only different numbers should be recorded. What do I have to do to change it to make it only record different numbers? Is it a new command instead of intersect?
Thanks again

 

[PHV]

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, [A1]) Is Nothing Then
     With Cells(Cells.Rows.Count,"B").End(xlUp)
       If .Value <> [A1].Value Then
         .Offset(1).Value = [A1].Value
       End If
     End With
   End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mark2Aus]

Hi PHV,
It works just fine. No more repeating numbers. Excellent.

But, to make things more challenging, as I expect the numbers of different numbers appearing on A1 to increase substantially, besides not recording repeated numbers anymore, I would like to record only numbers which shows turning points in the sequence, that is to record only numbers that represent a change in the direction the numbers were going. To be regarded a turning point, the change must be by at least 5 points.

Example: To define a direction at least 2 numbers must be recorded in B1 and in B2. Thus, if A1 shows 60, 60 is recorded in B1, then where A1 shows 62, 62 is recorded in B2 (indicating that the direction is Up), but when A1 shows again 61, nothing is recorded (it is agaisnt the direction but by only 1 point). But, if A1 now shows 63, 63 is then recorded in B2 (not in B3, as 63 is not yet a turning point). If then A1 shows 64, the same 64 is recorded still in B2 (as 64 is not yet a turning point) but if then A1 shows 59 (5 points against the direction the numbers were showing, which was to move higher) then 59 is recorded in B3, leaving 64 irrevocably recorded in B2, as 64 was a turning point. And so on.
Is this possible to do?
Thanks
Mark

 

[SkipVought]

Then here is what you need to do.

Write pseudocode that accuately describes what happens under various conditions. Not just an example, but if then else type statements.

Like this...
[tt]
'define your terms
'[new value] value in A1
'[prev value 1] last recorded value in column B
'[prev value 2] next to last recorded value in column B
'and whatever other values you will need to evaluate...

If [new value] is greater than [prev value 1] + 5 then
'describe what happens
Else if [new value] is greater than [prev value 1] + 5 then
'describe what happens
Else
'describe what happens
etc...
[/tt]
Be painfully detailed and specific.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mark2Aus]

Thanks Skip,

'[new value] value in A1
'[prev value 1] last recorded value in column B
'[prev value 2] next to last recorded value in column B
'[current direction] current direction recorded in C1 : +1 direction Up ; -1 direction Down
'Initial recording
record [new value] in B 1
record [new value] in B 2
if B 2 is greater than B 1 then record +1 in C 1
else if B 2 is greater then B 1 then record -1 in C 1
end if

'Routine for each [new value]
if C 1 is equal to +1 then
if [new value] is greater than [prev value 1] then record [new value] in B 2
else if [new value] is greater than [prev value 1] then
if [new value] is smaller than [prev value 1] -5 then record -1 in C 1 and record [new value] in B3
else if [new value] is smaller than [prev value 1] - 5 then do nothing
end if
end if
else if C 1 is equal to +1 then
if [new value] is smaller than [prev value 1] then record [new value] in B 2
else if [new value] is smaller than [prev value 1] then
if [new value] is greater than [prev value 1] + 5 then record + 1 in C 1 and record [new value] in B3
else if [new value] is greater than [prev value 1] + 5 then do nothing
end if
end if
end if
end sub

 

[SkipVought]

if B 2 is greater than B 1 then record +1 in C 1
else if B 2 is greater then B 1 then record -1 in C 1
end if

Please be check these statements. I believe that this MAY be what you mean, but I cannot read your mind...

if [prev value 1] is [b]greater than[/b] [prev value 2] then 
   [current direction] = 1
   else if [prev value 1] is [b]LESS THAN[/b] [prev value 2] then 
      [current direction] = -1
end if

Please check ALL your logic before we spin our wheels.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...also, regarding the three statements in question, what happens if [prev value 1] EQUALS [prev value 2]

Your thought process is sloppy!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mark2Aus]

Hi Skip,

Sorry. Only now I am starting to study VBA and I am very unsure about how to write a code, but you are right. It was what I meant when I wrote B2 > B1, meaning [prev value 1] greater than [prev value 2].

[prev value 1] cannot be equal to [prev value 2] (see contribution above by PHV (MIS). I did not touch this possibility because I thought this latest code would have to be integrated into the original code you wrote and PHV added later on.
Thanks
Mark

 

[Mark2Aus]

Hope the logic is now OK.

'[new value] value in A1
'[prev value 1] last recorded value in column B
'[prev value 2] next to last recorded value in column B
'[current direction] current direction recorded in C1 : +1 direction Up ; -1 direction Down

'Initial recording: until [prev value 1] and [prev value 2] have been recorded and initial [current direction] established
[new value] in B 1
If [new value] is <> [prev value 1] record [new value] in B 2
else if [new value] is equal to [prev value 1] do nothing
end if
If [prev value 1] is greater than [prev value 2] then [current direction] = 1
else if [prev value 1] is less than [prev value 2] then [current direction] = -1
end if

'Routine for each [new value]
If [current direction] is equal to +1 then
If [new value] is greater or equal than [prev value 1] then record [new value] in B 2
else if [new value] is smaller or equal than [prev value 1] -5 then [current direction] = -1 and record [new value] in B3
else if [new value] is greater than [prev value 1] - 5 then do nothing
end if
end if
else if [current direction] is equal to -1 then
If [new value] is smaller or equal than [prev value 1] then record [new value] in B 2
else if [new value] is greater or equal than [prev value 1] + 5 then [current direction] = 1 and record [new value] in B3
else if [new value] is smaller than [prev value 1] + 5 then do nothing
end if
end if
end if
end sub