Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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?

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

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)?

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.

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!

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.

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.

"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.

Related Topics:

- splitting textpane into two columns with a line between columns
- Is it possible to have limited number of rows and columns in a sheet
- Get Column by number
- find the number of working days (monday to firday) between two give
- Seeking the data in two columns
- Addition of two numbers
- how to generate a rondom number btween two ranges
- Whether the number is the power of two or not.
- changing the port number on JBOSS
- VBA to Change value in column AQ based on values in I, and AI
- Change the placement of the data spread in several columns
- Column Change - VBA coding
- Change Columns from Float to Nvarchar
- Converting a "Stringed" Number to a Number
- Converting a "Stringed" Number to a Number
- type casting /converting seconds to a days hours minutes seconds
- Select Columns by the Name of Column in the Header Row
- if condition with negative value
- Copying and Pasting Values From One Column To Multiple Columns Using
- Ratio of positive and negative text in learning dataset for text classification?
- Compute the sum of both positive and negative elements of an array
- Can I make all negative numbers have a value of zero?
- What every architect should now know about the Service Component Arc
- when i press the menu->display time the time must updates every second.
- Triggering a change Event based on field change of DDE link