Type Mismatch for a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cultgag - 23 Mar 2006 21:29 GMT

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

Signature

cultgag

Reply to this Message

Dave Peterson - 23 Mar 2006 22:50 GMT

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.

> Sheet1.Cells(11, col_Safety).Value = "=IF(INT(F11)=1,("
> + Sheet1.Cells(7, col_Safety) + "+(MOD(F11,1)*"
[quoted text clipped - 21 lines]
> cultgag's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22677
> View this thread: http://www.excelforum.com/showthread.php?threadid=525835

Signature

Dave Peterson

Reply to this Message