-1

I shall monitor the value of a robot in excel. Everytime the robot sends the value "1" to my computer (Switches between 1 and 0, because digital) which shows up in the cell A1 in excel, the value "3" shall be added in cell B1. Second time A1 changes to 1 the value shall be 6 in B1 etc.

The code below does just that, except that it adds the value 3 even when it switches FROM 1 TO 0. I only want it to switch when it changes FROM 0 TO 1.

Using Excel 2007

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A5")) Is Nothing Then Me.Range("B5").Value = Me.Range("B5").Value       + 3
Imolate
  • 1
  • 1

1 Answers1

0
If target.Column = 1 and target.Row = 1 then
    If target.Value = 1 Then
        Range("B1").Value = Range("B1").Value + 3
    End If
End if

This should do it.

Goos van den Bekerom
  • 1,475
  • 3
  • 20
  • 32
  • Thanks Goosebumbs, almost. I only want it to add 3 when A1 gets to 1. Not any targeted cell, as I will need to monitor 3 other things in the same way. But this solved the thing with not adding 3 when cell goes back to 0. – Imolate Sep 11 '14 at 08:06
  • I edited the answer, and you can do the same with the other 3 things you need. just change the column and row index number. – Goos van den Bekerom Sep 11 '14 at 08:13
  • 1
    Thanks alot Goosebumps. Works clean. :) – Imolate Sep 11 '14 at 08:21
  • Okay after some testing it appears it doesn't work as I want. I can't put the value from the robot in the same cell as the macro is reading. So I tried to put the value in another cell, and then just reading that cell from the cell used in the macro. But the macro only works if it's a 1 written manually, and not read from another cell. Do you understand? – Imolate Sep 11 '14 at 08:36
  • I think I don't understand :P EDIT: did you change the column and row index number in the code? – Goos van den Bekerom Sep 11 '14 at 08:38
  • Yes, it works as intended, long story short: The value 3 is only added when a 1 is written directly in the cell, and not when the cell used in macro is reading the value 1 from another. Is that more clear? haha – Imolate Sep 11 '14 at 08:43
  • you mean that when the robot fills the cell the code does not run? – Goos van den Bekerom Sep 11 '14 at 08:46
  • Yes, exactly. Cause the robot value can't be added directly to the Row-column-cell written in the macro, it gives an error. So I have to use a =SUM to read from the "robot-cell" to get the value. It's hard to explain. – Imolate Sep 11 '14 at 08:50
  • you can just change the row column cell to your robot cell, or if you use that =SUM it should also work since the worksheet changed. – Goos van den Bekerom Sep 11 '14 at 08:51
  • Yeah thought so too, but it appears that it will only add 3 if I write 1 directly into the cell. And if I add the robot cell to the row-column used in macro it says "Error nr '13': Incombatible types." – Imolate Sep 11 '14 at 08:58
  • Ah I see, 'incompatible types' means (I Think) that it is the wrong kind of variable, when you enter a 1 manualy it is considered text. like "1" you should declare a variable as integer ( or boolean if you want 1 to be true and 0 to be false) and then the 1 will be 1 instead of "1" – Goos van den Bekerom Sep 11 '14 at 09:05
  • basically, when you enter it manually it gets value: "1". and when the robot enters it it gets value: 1. I am guessing that's whats causing this – Goos van den Bekerom Sep 11 '14 at 09:07
  • Alright, thank you very much for giving me your time. I'm gonna try another solution. I don't think this is doable. :) – Imolate Sep 11 '14 at 09:12