Assigning string values to a range

[Risky Dave]

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

 

[Jim Thomlinson]

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))"

 

[Risky Dave]

dmoney,

Thanks for the response, I managed to find the errors - I hadn't paired the
brackets accurately.

FYI, although the formula does contain variables (NewRiskCounter), these
values are calculated before the string is passed to the cell defined by
Range(), which is why it works.

Dave

 

[Risky Dave]

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

 

[Barb Reinhardt]

That's usually how it works!

Barb Reinhardt