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?

 

[mortgageman]

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

I think my question could be better stated thusly:

In native excel, Excel "knows" that a formula should be handled as an array due to the "syntax" - the formula is confirmed with CSE instead of just E. In VBA, is there not some "dim" way of announcing the difference in use?

 

[jindon]

Maybe you are misunderstanding about "Array Function".

I mean Array functions are functions that calculate array basis such as;

Transpose, Sumproduct...etc.

 

[pgc01]

Hi all

This is a big post. User instructions: print it and take it to bed if you have difficulty sleeping. You'll be asleep in no time.

I'm glad you are dicussing the Evaluate method with array functions. I think it's a very powerful method and it's much less used that it should. Not many people use it sistematically, unless to refer to a range with the shorhand [A1], or to assign a range to an array but not understanding well its mechanics.

I've used it a lot and here are some ideas that I'd please ask you to criticise.

Gene's question is important. When does Evaluate calculate the expression as an array formula?

In the definition of Evaluate we see that Evaluate accepts directly a range, that's what allows us to do vArray=Range("A1:A5"), Evaluate converts the range to an array and then returns the result as an array. If, however, we use a function in the expression, Evaluate does not control what happens inside the function.

So it works if the function is a native array function (sumproduct, transpose, the matrix functions, etc.), and it doesn't, like in Richard's Upper(), for the other functions.
Knowing this, if we want to use Evaluate to calculate a formula as an array formula we must force the generation of an array.

The way I found to do it is to use an extra dummy range (or 2, in case of array formulas with rectangular ranges) outside the functions.
What happens is that Evaluate sees the range and converts it to an array, forcing the calculation of the other functions one cell at a time.

I'll use the Upper example:

Range("A1:A4")=Evaluate("upper(A1:A4)")
All A1:A4 will have UPPER(A1)

Now let's try:
Range("A1:A4") = Evaluate("if(row(1:4),upper(A1:A4))")

This time it works. the row(1:4) is dummy, it's always >0 and so the IF will consider it as true. However, the presence of row(1:4) outside the function upper() forces Evaluate to create the array {1,2,3,4} and to return an array of 4 values, calculating the upper function with each of the cells in A1:A4.

Generally, for a vertical vector like
Set rRng = Range("A1:A10")

This one liner converts it to uppercase
rRng = Evaluate("if(row(1:" & rRng.Count & "),upper(" & rRng.Address & "))")

We can use this to do a lot of things, For ex. we can initialise an array with iArray=[{1,2,3}], using a constant array.
We can also use Evaluate for a dinamic initialisation, like "Create and initialise an array with 10 positions initialised with 10.0-10.9 (start 10.0 and step 0.1)":

iArray = [transpose(10+(-1+row(1:10))/10)]

There's the Sumproduct Worksheetfunction (never seen it used in vba). Although we could use directly [SumProduct(--)...] if we want to use the Sumproduct Worksheetfunction, for example SumProduct(--(rRng1=3),rRng2)

Set rRng1 = Range("E1:E5")
Set rRng2 = Range("F1:F5")

dSumProduct = Application.WorksheetFunction.SumProduct(Evaluate("--(" & rRng1.Address & "=3)"), rRng2)


Until now I've only used a vector, but we can use it with a rectangular range. We just have to use 2 orthogonal vectors, either a row() and a column() or a row and a transpose(row()).

Example, we want an matrix 6x8 initialised with 1s ( a unit matrix)

iMatrix = [if(row(1:6),if(transpose(row(1:8)),1))]

This was a very simple example. A more interesting (and practical) example is a one liner you can use to look at the visible symbols of a font (codes 32 to 255).
This one liner fills A1:N32 with
- in the odd columns the code numbers, 32 in each column
- in the even columns the respective character

Please try:

Range("A1:N32") = Evaluate("IF(ROW(1:32),IF(MOD(TRANSPOSE(ROW(1:14)),2),ROW(1:32) -1+ 32*(TRANSPOSE(ROW(1:14))+1)/2,char(ROW(1:32) -1+ 32*TRANSPOSE(ROW(1:14))/2)))")

Well, these were some things I wanted to share about Evaluate. I've never seen most of this anywhere else and so, if anyone got this far, please comment. There may be errors and I'd like to correct them.

I hope this helps the discussion.

Cheers
PGC

 

[jindon]

Good work, PGC

One of the reason that I don't use this so much is

1) not good at formulas.

2) this method is hardly adjastable to a complex conditions.

maybe this is useful the situation like

Sub test()
Range("a1:c3") = [{1,2,3;4,5,6;7,8,9}]
Range("a4:c5") = [{"=sum(a1:a3)";"=average(a1:a3)"}]
End Sub

 

[pgc01]

Thank you Jindon.

You are right. This method is not suitable to complex conditions.

It may also not be easy to read. Most times a loop and some vba statements take more space but are easier to read and make a better structured code.

I must confess that maybe the reason I like Evaluate is that I like complex formulas and I find it fun to build complex one liners.

Cheers
PGC

 

[Richard Schollar]

Hey PGC - I really liked the post on Evaluate. Also, now I know how to use it to Upper a column of values

...I like complex formulas and I find it fun to build complex one liners.
~PGC01

Really? I hadn't noticed...

(A private joke relating to a PM I sent to PGC a couple of months back, just in case anyone thinks I'm being cheeky)

 

[pgc01]

Thank you, Richard.

Best regards
PGC

 

[sweater_vests_rock]

a really nice explanation from pgc01:

http://www.mrexcel.com/board2/viewtopic.php?p=1235881#1235881

edit:

a really nice explanation from pgc01:

http://www.mrexcel.com/board2/viewtopic.php?p=1235881#1235881

You have made of nice infinite loop there!

haha, I didn't see that this post had a second page! sorry!

 

[mortgageman]

a really nice explanation from pgc01:

http://www.mrexcel.com/board2/viewtopic.php?p=1235881#1235881

You have made of nice infinite loop there!

 

[ilcaa]

i found the Evaluate function in the Excel gurus Gone Wild which brought me to this thread..

I am using this formula which works great on functions like TRIM, UPPER, etc

rngRectangle = Evaluate("IF(ROW(" & rngRows.Address & "),IF(COLUMN(" & rngColumns.Address & "),upper(" & rngRectangle.Address & ")))")


i wanted to use it for horizontal alignment IsNumeric() exists within the range. I am having trouble properly formatting the wording and receive #VALUE! error messages. any help on this?

thanks.