Formula

[ccastaldi]

Why is that the VBA shows an error log when I try to insert this formula:

ActiveCell.Offset(j - 1, 3).Formula = Var1 & "/" & ActiveCell.Offset(j - 1, 2) * Lote

if Lote = 1 >>> theres no error
if lote < 1 >>> theres an error!

I also tryed:

ActiveCell.Offset(j - 1, 3).Formula = Var1 & "/" & ActiveCell.Offset(j - 1, 2) & "*" & Lote

and reached the the same results

does anyone has a clue?

tks
Cesar

 

[ccastaldi]

The exactly formula is:

ActiveCell.Offset(j - 1, 3).Formula = "=SUMPRODUCT((('[MileHigh LiveBook.xls]Trades'!B2:B1000)=" & ActiveCell.Offset(j - 1, 1).Address & ")*(('[MileHigh LiveBook.xls]Trades'!E2:E1000)=" & ActiveCell.Offset(j - 1, 0).Address & ")*(('[MileHigh LiveBook.xls]Trades'!K2:K1000)>0),('[MileHigh LiveBook.xls]Trades'!K2:K1000))/" & ActiveCell.Offset(j - 1, 2) * Lote

The destination doesnt seem to be the problem, because if take 'Lote' out of the formula, it works well.

 

[Norie]

No, I mean the actual formula as it should appear on the worksheet.

It also might help if we knew what the active cell was when you get the error.

If you want Lote to appear in the formula on the worksheet the syntax should be this.

ActiveCell.Offset(j - 1, 3).Formula = "=SUMPRODUCT((('[MileHigh LiveBook.xls]Trades'!B2:B1000)=" & ActiveCell.Offset(j - 1, 1).Address & ")*(('[MileHigh LiveBook.xls]Trades'!E2:E1000)=" & ActiveCell.Offset(j - 1, 0).Address & ")*(('[MileHigh LiveBook.xls]Trades'!K2:K1000)>0),('[MileHigh LiveBook.xls]Trades'!K2:K1000))/" & ActiveCell.Offset(j - 1, 2) & "*" & Lote

Also I think the problem is with the cell reference.

Next time you get the error, choose debug and then goto the immediate window.(CTRL+G)

Enter ? followed by everything after Formula =.

Does that return a valid formula?

 

[ccastaldi]

The immediate window returns the following:

=SUMPRODUCT((('[MileHigh LiveBook.xls]Trades'!B2:B1000)=$B$5)*(('[MileHigh LiveBook.xls]Trades'!E2:E1000)=$A$5)*(('[MileHigh LiveBook.xls]Trades'!K2:K1000)>0),('[MileHigh LiveBook.xls]Trades'!K2:K1000))/0,0303

Thats exactly what I want. It works if I copy it to the cell, changing ',' for ';' (syntaxes are different in Excel and VBA).

when I get error, the activecell reference is blank

 

[hatman]

Umm, what's that 0,0303 at the end? Should it be 0.0303? When I enter =1/0,0303 in excel here it gives me an error... I know that in some countries the comma is used in place of the decimal... but is Excel configured to accept it?

 

[Norie]

In VBA you must use the American decimal/thousand seperators.

This could explain why it works when Lote is 1, because then there wouldn't be any seperator.

So now I'm thinking the problem might be with how you've defined Lote and how you've given it a value.

 

[ccastaldi]

the 0.0303 is a number, and excel recognize it. I've also tryed to input the number instead the variable, but i got the same results.

 

[Norie]

As far as I know you must use . as a decimal seperator in VBA.

You say that the formula included 0,0303 when you checked it in the immediate window?

If that's the case that's probably the error.

If you just copied the formula from the immediate window then Excel would probably accept it and do the conversion, VBA won't.

 

[ccastaldi]

I've tryed both ways: 0,0303 and 0.0303, using variables to insert formula, and using the number itself. None of them worked. The error reminds me that kind of error that occurs when you try insert a large number in a variable set as double. But here, cell's configuration theres nothing to be with it. I really have no clue what to do.

 

[Norie]

One idea might be to post the rest of your code, and perhaps some sample data.

It's kind of hard diagnosing a problem from 1 line of code.

 

[hatman]

Norie, could VBA possibly be picking up the comma from the format of the ActiveCell.Offset(j - 1, 2)? I know it doesn't make a lot of sense... but is it possible? I'm wondering if this thing evaluates to an integer when he leaves the Lote off, so it masks the problem, but when Lote is used, it evaluates to a decimal, and carries the format along regardless of the format of Lote...