Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Change every second column to a negative number

  Asked By: Jasmine    Date: Aug 26    Category: MS Office    Views: 1315
  

I am trying to make a roll book for school using excel. I need every
second column to be negative (i.e. I use 1 column for positives, 1 for
negatives). I found code online to make a column negative:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("F3:F30")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value * -1
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

If I add a second column to the F's ("h3:h30") it is fine. Once I add
a third column ("j3:j30"), it doesn't work, and says that there is a
"wrong number of arguments or invalid property assignment".

How can make every second column be negative?
Would I use cell instead of range, and if so, how do I make it every
second cell?

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Elaine Stevens     Answered On: Aug 26

I think I can simplify this.
A couple of things, though.
This doesn't make the value negative,
It REVERSES the sign of the value that is entered!
if a user enters -5, the line: .Value = .Value * -1
will change  the number  to 5!
How about this:
Target.Column gives the column  number.
since you're starting in column "F" (column 6)
subtracting 6 from the target.column will give you
the first column you're monitoring as 0 and every other
column will be an even number.
using the "mod" function, and checking for a remainder
will tell you if the number is even or odd.
so.....
Private Sub Worksheet_Change(ByVal target  as Range)
if ((target.value = "") or (target.column < 6)) then exit sub
On error  goto ws_exit:
if ((target.column - 5) mod 2 = 0) then
Application.EnableEvents = False
Range(target.address).value = abs(target.value) * -1
Application.EnableEvents = True
end if
ws_exit:
Application.EnableEvents = True
End Sub
Another thing to do is use:
If ((Not Intersect(Target, Me.Range("F3: F30")) Is Nothing) _
or (Not Intersect(Target, Me.Range("H3: H30")) Is Nothing) _
or (Not Intersect(Target, Me.Range("J3: J30")) Is Nothing) _
or (Not Intersect(Target, Me.Range("L3: L30")) Is Nothing) _
or (Not Intersect(Target, Me.Range("N3: N30")) Is Nothing) _
) Then

 
Answer #2    Answered By: Alexis Castillo     Answered On: Aug 26

Thank you so much for the reply, I was not able to work  on this for a
few days, so I did not have a chance to try it out.
I tried it and it nearly worked.
It was making the wrong column  into negative, so based on your
explanation I changed (target.column - 5)to be (target.column - 6)and
it worked. Thank you so much!

I dont yet fully understand how this works, so I have another similar
question.
Is there a way to do this for every 18th column (For a different
worksheet)?

 
Answer #3    Answered By: Dot net Sachin     Answered On: Aug 26

Absolutely.
The "mod" function tells you the REMAINDER
after two numbers are divided.
In the case you've completed,
you take the column  number, say 15..
divide it by two.
It goes into 15 seven times, leaving (1) left over.
since 1 does not equal 0, then the
If statement: if(# mod 2 = 0) is false.
For every 18 columns/rows,
simply use if(Target.column mod 18 = 0) the
This would then be true for column 18, 36, 54, 72, etc.

 
Answer #4    Answered By: Renee Lane     Answered On: Aug 26

Thank you so much.
Now, if I enter a number  into the column  which comes out negative, and
then delete it, it has a 0. Clearing the contents of the cell  does not
help. It is not a big issue, but I am wondering if it can be fixed.
If I do not enter anything into the cell, it is clear.
I have the "show zero value" checked, because I need it for other
places, but even if I take it off, the 0 still affects the formula.
That negative  cell is summed with another cell. IF they are both
blank, they have a formula to say "N/A". However if enter something
into the negative cell and then need to delete it, the result is not
N/A because it still has a 0.

I hope that is somewhat clear!
Again, if it is complicated to fix, the roll  book will still be fine.
Once again I thank you for making my preparations for school easier!

 
Answer #5    Answered By: Volney Fischer     Answered On: Aug 26

I don't have any way of knowing what the rest of your code
is doing.
But within this section, you've got a
If (target.column mod "some number" = 0) then
...
...
...
end if
in this section, you're testing the target.value
and doing SOMETHING..
using Debug to step through the code  should tell
you what part of the code is actually putting a value in the cell.
but..
Right after the first IF... statement,
if you put:
If (target.value <> "") then
(remember to put the "End If" at the end)
I've sometimes had the above if statement return "null" instead of
"", so I usually use:
If (Target.Value & "X" <> "X") then
give that that a try.

 
Answer #6    Answered By: Sophie Campbell     Answered On: Aug 26

The code  is not putting anything into the cell, the problem is that if
I manually put something in, I cannot delete it.

However I discovered that if I delete it together with another cell,
it does delete completely!
Thank you so much for your help.

 
Answer #7    Answered By: Adalwine Fischer     Answered On: Aug 26

"Deleting it" IS changing the cell  and causing
it to trigger the change  event.
Your code  is then acting on the changed cell
and doing SOMETHING.
The result of that SOMETHING is that it is
putting a value of "0" in the cell!
The fact that you can delete two cells at the same
time and it DOESN'T result in "0" reinforces this
conclusion.
Try using Debug.
You'll see that when you select two cells and delete
the contents, the Change event executes with the
Target.Column value being leftmost column  of the cells selected.
The Target.Value is then an ARRAY of values.
Your code then works based on this situation and
is probably not working as you would expect.
But then, if you're happy, I'm happy.

 
Didn't find what you were looking for? Find more on Change every second column to a negative number Or get search suggestion and latest updates.




Tagged: