error passing a formula to a cell from a string
 

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

--
TheIrishThu
-----------------------------------------------------------------------
TheIrishThug's Profile: http://www.excelforum.com/member.php...fo&userid=2968
View this thread: http://www.excelforum.com/showthread.php?threadid=49542

error passing a formula to a cell from a string
 

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

"TheIrishThug" wrote:


buyin, buyinmod, datemod, and fieldmod r all strings. the following are
4 expample of formulas i am trying to pass, but am getting
"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 < BuyinMod
Then
Sheets("Tourney Info").Range("K" & Row).Formula = BuyinMod
End If
If Sheets("Tourney Info").Range("L" & Row).Formula < FieldMod
Then
Sheets("Tourney Info").Range("L" & Row).Formula = FieldMod
End If


--
TheIrishThug
------------------------------------------------------------------------
TheIrishThug's Profile: http://www.excelforum.com/member.php...o&userid=29682
View this thread: http://www.excelforum.com/showthread...hreadid=495427

error passing a formula to a cell from a string
 

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

In article ,
TheIrishThug
wrote:

buyin, buyinmod, datemod, and fieldmod r all strings. the following are
4 expample of formulas i am trying to pass, but am getting
"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 < BuyinMod
Then
Sheets("Tourney Info").Range("K" & Row).Formula = BuyinMod
End If
If Sheets("Tourney Info").Range("L" & Row).Formula < FieldMod
Then
Sheets("Tourney Info").Range("L" & Row).Formula = FieldMod
End If

error passing a formula to a cell from a string
 

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

--
TheIrishThu
-----------------------------------------------------------------------
TheIrishThug's Profile: http://www.excelforum.com/member.php...fo&userid=2968
View this thread: http://www.excelforum.com/showthread.php?threadid=49542