Equation to UDF

[summer10]

I have a long equation that I will be using over and over in an excel sheet but I have not programmed in VBA before. I am very good at Java though.

my equation is this:

ROUND(-SUMIF('[FileName.xlsx]Data'!$V:$V,$A13&$B13&$C$7&LEFT($C$11,3),'[FileName.xlsx]Data'!$I:$I),-3)/1000

To explain:
ROUND(
-SUMIF(
'[FileName.xlsx]Data'!$V:$V, [long column with string of IDnumber (x), year (Y), and category(Z).... so an example of a value is XXXXYYYYZZZ]
$A13&$B13&$C$7&LEFT($C$11,3), [Column A = IDnumber(x), Column B = year(Y), column C= category(Z)]
'[FileName.xlsx]Data'!$I:$I) return the sume of the finding's values in column I
,-3) rounding to thousands

Can anyone help???

[foxguy]

Why do you want to put it into a UDF()? Once you have the formula it's easy to just copy & paste it into the cells you want it in.

That said:
VBA functions return a value by putting the value into the function name. IOW when you change the function name, be sure and change all the lines that say "myUDF = " to the new name
Replace the <...> with any # that would indicate something to you.
Example
-99 might mean the workbook couldn't be found
101 might mean the sheet wasn't found
-1 might mean some other error occurred
If there's an error, you don't want myUDF() popping up a message every time it calculates.

Please Login or Register  to view this content.

I didn't debug this except to make sure there were no syntax errors. I've never used sumif across 2 workbooks. I'm just assuming that the syntax for that formula is correct

From cell J13 you would call it with the formula =myUDF(J13, "FileName")

If FileName will always be the same file, then you could remove the Filename parameter.

To put this into your workbook:

Please Login or Register  to view this content.

Final note:
You might find it easier to put the sumif into FileName.xlsx, then just point to the proper cell from the current workbook.