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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
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,884
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
76,304
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
76,304
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.
 

Forum statistics

Threads
1,141,663
Messages
5,707,691
Members
421,524
Latest member
Bharath99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top