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

:)


--
cultgag
------------------------------------------------------------------------
cultgag's Profile: http://www.excelforum.com/member.php...o&userid=22677
View this thread: http://www.excelforum.com/showthread...hreadid=525835

Type Mismatch for a formula
 

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