Type Mismatch for a formula

Sheet1.Cells(11, col_Safety).Value = "=IF(INT(F11)=1,("
+ Sheet1.Cells(7, col_Safety) + "+(MOD(F11,1)*"
+ Sheet1.Cells(7, col_Safety + 1) + ")),IF(INT(F11)=2,("
+ Sheet1.Cells(7, col_Safety + 1) + "+"
+ Sheet1.Cells(7, col_Safety + 2) + "+(MOD(F11,1)*"
+ Sheet1.Cells(7, col_Safety + 2) + "))))"

This formula keeps on giving me a Type Mismatch error...
I'm using this in a macro instead of a formula because this is a weekly schedule and I don't want the numbers changing when it's not the current date.

Can anyone see why I'm getting this error?

Any help will be greatly appreciated!

TIA

Try using & to concatenate text and use + to add numbers:

Option Explicit
Sub testme01()
Dim myFormula As String
Dim col_Safety As Long
col_Safety = 8
myFormula = "=IF(INT(F11)=1,(" _
& Sheet1.Cells(7, col_Safety) & "+(MOD(F11,1)*" _
& Sheet1.Cells(7, col_Safety + 1) & ")),IF(INT(F11)=2,(" _
& Sheet1.Cells(7, col_Safety + 1) & "+" _
& Sheet1.Cells(7, col_Safety + 2) & "+(MOD(F11,1)*" _
& Sheet1.Cells(7, col_Safety + 2) & "))))"

Debug.Print myFormula
End Sub

And with some test data in those cells, I got a formula like:

=IF(INT(F11)=1,(6+(MOD(F11,1)*17)),IF(INT(F11)=2,(17+8+(MOD(F11,1)*8))))

And I'm not sure that's correct, either.

cultgag wrote:
>
> Sheet1.Cells(11, col_Safety).Value = "=IF(INT(F11)=1,("
> + Sheet1.Cells(7, col_Safety) + "+(MOD(F11,1)*"
> + Sheet1.Cells(7, col_Safety + 1) + ")),IF(INT(F11)=2,("
> + Sheet1.Cells(7, col_Safety + 1) + "+"
> + Sheet1.Cells(7, col_Safety + 2) + "+(MOD(F11,1)*"
> + Sheet1.Cells(7, col_Safety + 2) + "))))"
>
> This formula keeps on giving me a Type Mismatch error...
> I'm using this in a macro instead of a formula because this is a weekly
> schedule and I don't want the numbers changing when it's not the current
> date.
>
> Can anyone see why I'm getting this error?
>
> Any help will be greatly appreciated!
>
> TIA
>
>
>
> --
> cultgag
> ------------------------------------------------------------------------
> cultgag's Profile: http://www.excelforum.com/member.php...o&userid=22677
> View this thread: http://www.excelforum.com/showthread...hreadid=525835

--

Dave Peterson