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

 

[Norie]

Cesar

What are Var1 and Lote?

Are you actually trying to insert a formula into the cell?

 

[hatman]

Which error number does it throw?

 

[pgc01]

Hi Cesar

Aren't you forgetting the initial equal sign of the formula?

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

HTH
PGC

 

[hatman]

HAH, can't believe I didn't notice that!

 

[Norie]

Hatman

I don't think that's the problem.

The OP seems to be concatenating to create a formula, then trying to multiply the resultant string be Lote.

That's what it looks like to me anyway.

 

[hatman]

MMM, You are right that it is not the problem (though I still shoudl have spotted it) but although he is concatenating a formula together, his two methods will yield different strings.

Method 1 will cause VBA to perform the last bit of multiplication before inserting the text string while Method 2 will not. He has stated that Lote is a number , and we can assume that Var1 and ActiveCell.Offset(j - 1, 2).value are also numbers , so the resulting string in method 1 would be "n/n" while method 2 will yield "n/n*n". Either way, VBA allows you to assign a text string to the .Formula property, which is what he is doing (probably by mistake). Method 1 would be more likely to throw an error, since VBA is performing a calculation (multiply text by a number, let's say), while Method 2 should not be throwing an error regardless...

Sub stuff()

    j = 3
    var1 = "stuff"
    lote = "stuff2)"

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

End Sub

Thows a Type Msmatch Error, while

Sub stuff()

    j = 3
    var1 = "stuff"
    lote = "stuff2)"

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

End Sub

Just blindly inserts the test string "stuff/*stuff2)" into a cell. Now, if j takes on a value relative to the activecell that throws the destination cell off of the sheet, THAT's a problem... it'll throw an Application Defined Error, and to me it's the most likely to be causing a problem with BOTH methods.

 

[ccastaldi]

Yes, I'm trying to insert a formula into a cell.

'Var1' and 'Lote' are numbers. The main problem remains on Lote, I've tested the code without using 'Lote' and it worked.

I really forgot to use "=" here in the example, but I used it in the code. So I get:

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

The error number is 1004.

In fact, I realized that if 'Lote' < 0.1, it doesnt work.

 

[Norie]

Does this work?

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

PS Can you tell us the formula you are trying to insert?

Also what's the value of j when you get the error?

 

[hatman]

Error 1004 is Application Defined Error. More likely your problem is when your combination j and the Activecell.address sends the destination cell off the sheet.