error passing a formula to a cell from a string

[TheIrishThug]

buyin, buyinmod, datemod, and fieldmod r all strings. the following ar
4 expample of formulas i am trying to pass, but am gettin
"application-defined or object defined" errors.
can anyone see where my mstake is?


Buyin = "=(Sheets(""Tourney Info"").Range(""D" & Row
""").Value)/(Sheets(""Tourney Info"").Range(""E" & Row & """).Value)"
BuyinMod = "=LN((Sheets(""Tourney Info"").Range(""G" & Row
""").Value)/100+1)"
FieldMod = "=(Sheets(""Tourney Info"").Range(""E" & Row
""").Value)/10-(Sheets(""Tourney Info"").Range(""E" & Row
""").Value)^2/20000"
DateMid = "=36/(MONTH(NOW())-(Sheets(""Tourney Info"").Range(""B"
Row & """).Value)+12*(YEAR(NOW())-(Sheets(""Tourney Info"").Range(""C"
Row & """).Value))+36)"

If Sheets("Tourney Info").Range("G" & Row).Formula <> Buyin Then
Sheets("Tourney Info").Range("G" & Row).Formula = Buyin
End If
If Sheets("Tourney Info").Range("J" & Row).Formula <> DateMod Then
Sheets("Tourney Info").Range("J" & Row).Formula = DateMod
End If
If Sheets("Tourney Info").Range("K" & Row).Formula <> BuyinMo
Then
Sheets("Tourney Info").Range("K" & Row).Formula = BuyinMod
End If
If Sheets("Tourney Info").Range("L" & Row).Formula <> FieldMo
Then
Sheets("Tourney Info").Range("L" & Row).Formula = FieldMod
End I

 

[Toppers]

Hi,
Try these changes:

buyin = "='Tourney Info'!D" & Row & "/'Tourney Info'!E" & Row & ""
buyinmod = "=LN('Tourney Info'!G" & Row & "/100+1)"
FieldMod = "='Tourney Info'!E" & Row & "/10-'Tourney Info'!E" & Row & _
"^2/20000"
Datemid = "=36/(MONTH(NOW())-('Tourney Info'!B" & _
"+12*(YEAR(NOW())-('Tourney Info!'C" & Row & "+36)"


HTH

 

[JE McGimpsey]

XL has no idea what to do with "Sheets(""Tourney Info"").Range("D" &
Row...". References need to be resolved outside the quoted string.

Try:

Buyin = "=" & Sheets("Tourney Info").Range("D" & Row).Value & _
"/" & Sheets("Tourney Info").Range("E" & Row).Value


or, a bit more efficiently:

With Sheets("Tourney Info")
Buyin = "=" & .Range("D" & Row).Value & _
"/" & .Range("E" & Row).Value
BuyinMod = "=LN(" & .Range("G" & Row) & "/100+1)"
'etc
End With

TheIrishThug

 

[TheIrishThug]

thanks to both of you. and i found how xl writes references to di
worksheets, so i'm good to go