Cell Formula

[VinNibs]

Do you know what is wrong with the following

Sub CellFormula()

Dim FormulaNo As Integer
Dim i As Integer

FormulaNo = 10
i = 10

Cells(i, 13).Formula = "=" & (Cells(i, FormulaNo)) & "/" & (Cells(i, 12))

End Sub

I'm getting Applicatio-defined or object-defined error

Thanks

 

[btomjack]

Cells(i, 13).Formula

The underlined portion is giving you the error. Try this instead:

Cells(i, 13).Value

 

[VinNibs]

Thanks for your response.

Cells(i, 13).Value is giving me the same error.

Anyway it needs to be a formula not value as the cell needs to change as other values in the sheet change.

 

[btomjack]

Ah, I wasn't aware that .Formula was a valid property of a range. In that case, I think you just need to tell Excel which sheet you're talking about. What is the name of the sheet this cell is in? For the next example, I'm just going to assume that it's Sheet1.

Change this:
Cells(i, 13).Formula

To this:
Worksheets("Sheet1").Cells(i, 13).Formula

If the desired worksheet is not named Sheet1, you'll have to swap out Sheet1 for the correct name of the sheet, but you WILL need to keep the quotation marks.

 

[bbott]

Sub CellFormula()

Dim FormulaNo As Integer
Dim i As Integer

FormulaNo = 10
i = 10

Cells(i, 13).Formula = "=" & (Cells(i, FormulaNo)) & "/" & (Cells(i, 12))

End Sub

This code worked when I ran it. What is in cells J10 and L10 in your worksheet?

 

[bbott]

Actually, try running this and see if this is what you want it to do.

Sub NewCellFormula()
    
       Range("m10").FormulaR1C1 = "=RC[-3] / RC[-1]"
    
End Sub

 

[VinNibs]

Thanks bbott for your persistence I was able to edit my variables so as to allow me to use ranges, and solve the problem.

Thanks for the help.