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

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

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

"dmoney" wrote:

> It seems that the formula contains variables. I think you need to do the
> calculations outside of the string expression as the cell you are populating
> cannot reference your code to determine what the variable means.
>
> HTH
>
> dmoney
>
> "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
> >
> >
> >

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

That's usually how it works!

Barb Reinhardt



"Risky Dave" wrote:

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