Jim,

Thanks managed to find it about 5 minutes after I posted - only taken about
2 1/2 hours tryig to sort this out!

Dave

"Jim Thomlinson" wrote:

You have an extra comma and missing a bracket...

Formula2 = "=(COUNTIF(C" & NewRiskCounter + 3 & ":Q" & NewRiskCounter + 3 &
", ""adequate"")+(2*COUNTIF(C" & NewRiskCounter + 3 & ":Q" & NewRiskCounter +
3 & ", ""improvement Required""))+(3*COUNTIF(C" & NewRiskCounter + 3 & ":Q" &
NewRiskCounter + 3 & ", ""weak"" ))/(15-COUNTBLANK(C" & NewRiskCounter + 3 &
":Q" & NewRiskCounter + 3 & ")/3))"
--
HTH...

Jim Thomlinson


"Risky Dave" wrote:

Hi,

As part of a larger function, I am putting a quite complex formula into a
specific cell on a sheet, but the compiler is giving me the good old
'Run-time error 1004:' which I thought indicated that i had made a typo
somewhere. Cant find it though :-(

The relevant lines are (apologies for the length of the formula!):

Dim NewRisk As Range
Dim NewRiskCounter As Integer
Dim Formula2 As String

Formula2 = "=(COUNTIF(C" & NewRiskCounter + 3 & ":Q" & NewRiskCounter +
3 & "," & Chr(34) & "adequate" & Chr(34) & ")+(2*COUNTIF(C" & NewRiskCounter
+ 3 & ":Q" & NewRiskCounter + 3 & "," & Chr(34) & "improvement Required" &
Chr(34) & "))+(3*COUNTIF(C" & NewRiskCounter + 3 & ":Q" & NewRiskCounter + 3
& "," & Chr(34) & "weak" & Chr(34) & "))/(15-COUNTBLANK(C" & NewRiskCounter +
3 & ":Q" & NewRiskCounter + 3 & ",)/3)"
Range("R" & NewRiskCounter + 3).Value = Formula2 ' This is where
the compiler is generating the error

What's really confusing me is that elsewhere I use what looks like an
identical approach and it works perfectly!

I assume that I've made a typo in the formula so if someone can point it out
for me I would very much appreciate it.

TIA

Dave