Getting Application Defined or object defined error for syntax Ws.Cells(1,3).Formula = .......

[NimishK]

I am getting following error
Getting Application Defined or object defined error in syntax below

ws.Cells(1, 3).Formula = "=(ws.Cells(1, 1).value) * (ws.Cells(1, 2).value)"

Pl dont show any .formula with range

thanks
NimishK

 

[mikerickson]

That is not a valid excel worksheet formula. Try

ws.Cells(1,3).Formula = "=$A$1*$B1"

 

[NimishK]

Very Spontaneous Reply Mikerickson sir, any other method using the ws.cells(1,2).value * ws.cells(1, 3).value
because i will be using the same in the loop

 

[pgc01]

Hi

Like Mike said that is not a valid formula.
The worksheet won't understand a formula like

=(ws.Cells(1, 1).value) * (ws.Cells(1, 2).value)


You must build the formula respecting the formula syntax, like

ws.Cells(1, 3).Formula = "=" & ws.Cells(1, 1).address & "*" & ws.Cells(1, 2).address

so that the resulting value for the formula will be something like

ws.Cells(1,3).Formula = "=$A$1*$B1"

which is understandable by the worksheet

 

[mikerickson]

What loop are you using to enter which (desired) formula?
I suspect that entering a single valid formula with relative referencing might eliminate the loop.

 

[NimishK]

Thanks PGC01 and Mike for your valuable inputs

The following is in the loop

''''Following Col nos BD=AV3+AY3+SUM(AZ:BC3)+AP3 to get the below formula
ws.Cells(curRow, 56).Formula = "=AV" & curRow & "+AY" & curRow & "+" & "Sum(AZ" & curRow & ":BC" & curRow & "+AP" & curRow

but difficult part is that i am not able to place as per BD=AV3+AY3+SUM(AZ:BC3)+AP3

 

[NimishK]

PGC01 Sir

Actually Absolute refernce in my case will not be appropriate as i fear of miscalculations. I will have to go with Range only
like

ws.Cells(curRow, 2).Formula = "=A1*B1"

Sirs will appreciate your help for Post 6

 

[pgc01]

Actually Absolute refernce in my case will not be appropriate as i fear of miscalculations. I will have to go with Range only
like

ws.Cells(curRow, 2).Formula = "=A1*B1"

Sirs ...will appreciate your help for Post 6

...will appreciate your help for Post 6

1 - You missed the closing parenthesis on the SUM()

ws.Cells(curRow, 56).Formula = "=AV" & curRow & "+AY" & curRow & "+" & "Sum(AZ" & curRow & ":BC" & curRow & "[COLOR=#800000][SIZE=3][B])[/B][/SIZE][/COLOR]+AP" & curRow

2 -

You can define the freezing in the cell address, like

MsgBox r.Address(RowAbsolute:=False, ColumnAbsolute:=False)

or simply

MsgBox r.Address(False, False)

 

[mikerickson]

Or you could use R1C1 notation and not involve curRow in the formula being entered.

ws.Cells(curRow, 56).FormulaR1C1 = "=RC48+RC51+SUM(RC52:RC55)+RC42"

You don't specify what the values curRow loops through, but (if the incriment is 1) you could use code like this without having to loop

ws.Range("BD1:BD10").FormulaR1C1 = "=RC48+RC51+SUM(RC52:RC55)+RC42"

 

[NimishK]

Thank you sirs for your valuable inputs.
I need to still go into depth of Formula R1C1. Have neve gone through it.
Thank you once more