Two problems inserting a formula

[Risky Dave]

Hi,

I have a piece of code that inserts a formula into a specific cell on a
line. The formula looks at five possible values on the same line, calculates
the average of the highest three of them and then uses this output (along
with another value) to look up a new value from a table (this is what the
offset does).

I am happy that the formula works as intended - when I enter it manually
into the sheet it works. What I can't work out is how to assign the formula
to the necessary cell - I keep getting an error when I use the code below:

sFormula = "=IF(ISERROR(OFFSET('User Data'!$D$13,-C" & lLineCount &
",ROUND((LARGE(D" & lLineCount & ":H" & lLineCount & ",1)+LARGE(D" &
lLineCount & ":H" & lLineCount & ",2)+LARGE(D" & lLineCount & ":H" &
lLineCount & ",3))/3,0)))," & Chr(34) & ",OFFSET('User Data'!$D$13,-C" &
lLineCount & ",ROUND((LARGE(D" & lLineCount & ":H" & lLineCount &
",1)+LARGE(D" & lLineCount & ":H" & lLineCount & ",2)+LARGE(D" & lLineCount &
":H" & lLineCount & ",3))/3,0)))"
Range("I" & lLineCount).Value = sFormula

Two questions:
1) what is the correct syntax to get the Range... line working?
2) the formula feels really clunky, can anyone suggest anything that
simplifies it?

TIA

Dave

 

[Jim Rech]

I think you need 2 Chr(34)'s:

Chr(34) & Chr(34)

--
Jim
| Hi,
|
| I have a piece of code that inserts a formula into a specific cell on a
| line. The formula looks at five possible values on the same line,
calculates
| the average of the highest three of them and then uses this output (along
| with another value) to look up a new value from a table (this is what the
| offset does).
|
| I am happy that the formula works as intended - when I enter it manually
| into the sheet it works. What I can't work out is how to assign the
formula
| to the necessary cell - I keep getting an error when I use the code below:
|
| sFormula = "=IF(ISERROR(OFFSET('User Data'!$D$13,-C" & lLineCount &
| ",ROUND((LARGE(D" & lLineCount & ":H" & lLineCount & ",1)+LARGE(D" &
| lLineCount & ":H" & lLineCount & ",2)+LARGE(D" & lLineCount & ":H" &
| lLineCount & ",3))/3,0)))," & Chr(34) & ",OFFSET('User Data'!$D$13,-C" &
| lLineCount & ",ROUND((LARGE(D" & lLineCount & ":H" & lLineCount &
| ",1)+LARGE(D" & lLineCount & ":H" & lLineCount & ",2)+LARGE(D" &
lLineCount &
| ":H" & lLineCount & ",3))/3,0)))"
| Range("I" & lLineCount).Value = sFormula
|
| Two questions:
| 1) what is the correct syntax to get the Range... line working?
| 2) the formula feels really clunky, can anyone suggest anything that
| simplifies it?
|
| TIA
|
| Dave