| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplication of string values within a range | Excel Worksheet Functions | |||
| Find a string in a range and return two values based on offsets | Excel Programming | |||
| Problem assigning values to a range of cells | Excel Programming | |||
| Assigning ranges for values (e.g., $1,005 the range is ">$1K to 5K | Excel Worksheet Functions | |||
| Assigning values from a selected range to individual variables | Excel Programming | |||
Hybrid Mode
