inverted commas

[Risky Dave]

Hi,

I want to insert an IF statement into a cell as an error check. This is done
as part of a much larger function.

The If statement is intended to make the cell blank in the event of an error
(ie it returns "") and I think this is the problem I am having. I have had a
look at Bob Phillips' reply to Mentos from 7/17/08 and can't work out what
(apart from the greater complexity I am doing differently.

The relevant lines are:

Dim NewRiskCounter As Integer
Dim GrossRangeScore As String
Dim ProbNumber As String
Dim Formula As String

Formula = "IF(ISERROR(((SUM(LARGE(" & GrossRangeScore & ",{1,2,3})))/3)*" &
ProbNumber & ")*4)," & ,(((SUM(LARGE(" & GrossRangeScore & ",{1,2,3})))/3)*"
& ProbNumber & ")*4))"
Range("I" & NewriskCounter).Value = Formula

The error message i am getting is:
Run-time error '1004':

Application-defined or object-defined error

I have also tried using:

ActiveCell.Formula = "=IF........"

but can't get that working either.

If anyone can:
a) Explain in simple terms (I'm not a programmer and am learning this stuff
as I go along) what the error message means
b) spot where I'm going wrong

I would be hugely grateful.

TIA

Dave

 

[Sam Wilson]

Hi, you do need the "=IF(" rather tham "IF"

The error is telling you that when the formula is entered it doesn't make
sense, just as if you typed a wonky formula into excel directly. You've got a
space comma in there.

Change

ProbNumber & ")*4)," & ,(((SUM(LARGE(" & GrossRangeScore & ",{1,2,3})))/3)*"

to

ProbNumber & ")*4),(((SUM(LARGE(" & GrossRangeScore & ",{1,2,3})))/3)*"

 

[Sam Wilson]

Just read your question properly - change:

ProbNumber & ")*4)," & ,(((SUM(LARGE(" & GrossRangeScore & ",{1,2,3})))/3)*"

to

ProbNumber & ")*4),"""",(((SUM(LARGE(" & GrossRangeScore & ",{1,2,3})))/3)*"

Ignore the first response.