Macr: Change Positive Values To Negative

[wayneshirley]

I use data on a text file and import this into Excel and use a macro to format. The data lists invoices and credit notes and their dollar value.

Due to a fault in the program that creates the text file all dollar amounts are shown as positive figures. Credit note amounts should correctly be shown as negative figures.

The invoice number format is 8 characters beginning with a letter and credit notes begin with a number.

Is there macro code that will change credit note values to a negative figure?

 

[jindon]

try

Sub test()
Dim rng As Range
Set rng = Range("d2",Range("d" & Rows.Count).End(xlUp))
rng.Value = Evaluate(rng.Address & "*-1")
Set rng = Nothing
End Sub

 

[wayneshirley]

Macro: Change Positive Values To Negative

Thanks for this code, Jindon. I have tried it but it changes all values in column D to a negative figure.

What I need is only the credit notes to be changed to a negative figure and all the invoices to remain unchanged.

Can you help please?

 

[jindon]

can you post few sample data?

 

[Richard Schollar]

Possibly try the following adaptation of Jindon's code - note that I have assumed that the invoice number/credit note number is in the column to the immediate left of column D (ie the C col):

Sub test()
Dim rng As Range, r As Range
Set rng = Range("d2", Range("d" & Rows.Count).End(xlUp))
For Each r In rng
If Left$(r.Offset(, -1), 1) Like "[0-9]" Then r.Value = r.Value * -1
Next r
Set rng = Nothing
End Sub

 

[Richard Schollar]

try

Sub test()
Dim rng As Range
Set rng = Range("d2",Range("d" & Rows.Count).End(xlUp))
rng.Value = Evaluate(rng.Address & "*-1")
Set rng = Nothing
End Sub

Jindon

Could you explain why the code does not need a loop (eg to apply to every cell individually) - I was very impressed that your code construct could be applied to a multi-cell range. However, it seems to fail if I attempt to use Evaluate with an Excel function (eg using UPPER) - in this case, the entire range is filled with the first cell's value (as amended by the function I am evaluating).

Thank you!

 

[jindon]

Richrad

That's true, because it is like array formula, but not exactly, and does not work properly with non-array functions.

Range("a1").Value = Join(Evaluate("Transpose(A2:A7)")," ")

does work.

It may useful in some occasions...

 

[mortgageman]

Richrad

That's true, because it is like array formula, but not exactly, and does not work properly with non-array functions.

Range("a1").Value = Join(Evaluate("Transpose(A2:A7)")," ")

does work.

It may useful in some occasions...

In the spirt of no question is a dumb question: How does it (the formula) "know" to be like, but not exactly, an array function?

 

[Richard Schollar]

Gene

I suspect that this depends on the function concerned (ie is it coded to accept arrays - as in my example, Upper obviously isn't) and also then on how the arguments to the function are passed (ie in a range or as a discrete single range/value). Sorry, above explanation probably hasn't enlightened you much...

 

[jindon]

OK

Put this way.

Evaluate method;

Range("a1:a10").Value = Evaluate("A1:A10+100")

used like array formula, it can hold individual calculated results for respective cells, however it doesn't work when non-array function comes in.
That's why I said "Not exactly".

As Richard experienced, it only returns the first result to the entire range.

I don't know if it works with "Sumproduct", "DSum" etc, because I'm not good at formulas, you know?

And also Evaluate method can create 1based 2 dim array like

myList = [{"a","b","c","d";1,2,3,4}]

[ ] are used for Evaluate.

FYI [a1] = 1 is Evaluate("a1").Value = 1

hope this helps