|
Subject:
|
Inserting formula
|
|
Posted By:
|
slgknjn
|
Post Date:
|
1/25/2006 2:14:15 AM
|
Hi all
I ran into a problem that I cannot solve myself.
I would like to enter a formula in some cells using VBA
Cells(2, 19).Formula = "=IF('2006-1'!I2<>"";'2006-1'!I2;"")"
Where 2006-1 is another sheet in the workbook.
If I just put some simple formula in there is no problem, but the above one gives me the following error.
Application-defined or object-defined error
Any help is appriciated.
Cheers
Karsten
|
|
Reply By:
|
slgknjn
|
Reply Date:
|
1/25/2006 2:46:00 AM
|
Hi again
I solved my problem.
Sub insertFormula()
Dim RowNo As Integer
Dim J As Integer
RowNo = 2
For J = 5 To 107 Step 2
Cells(J, 19).Formula = "=IF('2006-1'!I" & RowNo & ">0,'2006-1'!I" & RowNo & ","""")"
RowNo = RowNo + 1
Next J
End Sub
|
|
Reply By:
|
echovue
|
Reply Date:
|
1/25/2006 2:47:53 AM
|
Sweet - I was halfway through typing up the very same solution!!
Mike
EchoVue.com
|
|
Reply By:
|
mjppaba
|
Reply Date:
|
1/25/2006 2:52:08 AM
|
I was about to say, the formula need to have a quadruple " if it is to be recoginised as a string which is to be embedded into a formula...
"=IF('2006-1'!A2<>"""",'2006-1'!A2,"""")"
But then again looks like you figured it out anyhow. There is an alternative which I prefer...
Cells(J, 19).Formula = "=IF('2006-1'!I" & RowNo & ">0,'2006-1'!I" & RowNo & "," & Chr$(34) & Chr$(34) & ")"
That is, to use the ASCII character code set to specify the Chr$(34) i.e. ", then concatenate this to the rest of the formula. But that is only my preference.
cheers
Matt
|
