Formula

ccastaldi

New Member
Joined
Apr 28, 2006
Messages
20
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Cesar

What are Var1 and Lote?

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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 (n), and we can assume that Var1 and ActiveCell.Offset(j - 1, 2).value are also numbers (n), 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...

Code:
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

Code:
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

New Member
Joined
Apr 28, 2006
Messages
20
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Does this work?
Code:
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,037
Messages
5,545,663
Members
410,697
Latest member
srishtijain0708
Top