Assigning string values to a range
 

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

Assigning string values to a range
 

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

Assigning string values to a range
 

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

Assigning string values to a range - SOLVED
 

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

Assigning string values to a range
 

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

Assigning string values to a range
 

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