ExcelBanter - Run time 1004

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Run time 1004 (https://www.excelbanter.com/excel-discussion-misc-queries/254112-run-time-1004-a.html)

I am trying to use the Reuters quote function in a macro. I have created a
string that gets the symbols and date from cells of the excel file. I can
successfully have the strinf displayed in a message box to show that I have
successfully created the function for reuters. However when I attempt to
have this inputed into a cell I get the runtime 1004 error. can you help?
thanks

Sub valuation()

Dim tdate As String
Dim symbol As String
Dim price As String
Dim valuation As String
Dim f1 As String
Dim f2 As String
Dim f3 As String
Dim f4 As String
Dim f5 As String
Dim f6 As String

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

valuation = ActiveCell.Offset(0, 3)

f2 = ",,"
f3 = ","
f4 = "close"
f5 = Chr(34)

f1 = "=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

f6 = f1 & f2 & symbol & f3 & f5 & f4 & f5 & f3 & tdate & f3 & f5 & f5

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

MsgBox (f6)

ActiveCell.Offset(0, 2) = f6

End Sub

Perhaps change the last line to:
ActiveCell.Offset(0, 2).Formula = f6
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"cluckers" wrote:

I am trying to use the Reuters quote function in a macro. I have created a
string that gets the symbols and date from cells of the excel file. I can
successfully have the strinf displayed in a message box to show that I have
successfully created the function for reuters. However when I attempt to
have this inputed into a cell I get the runtime 1004 error. can you help?
thanks

Sub valuation()

Dim tdate As String
Dim symbol As String
Dim price As String
Dim valuation As String
Dim f1 As String
Dim f2 As String
Dim f3 As String
Dim f4 As String
Dim f5 As String
Dim f6 As String

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

valuation = ActiveCell.Offset(0, 3)

f2 = ",,"
f3 = ","
f4 = "close"
f5 = Chr(34)

f1 = "=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

f6 = f1 & f2 & symbol & f3 & f5 & f4 & f5 & f3 & tdate & f3 & f5 & f5

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

MsgBox (f6)

ActiveCell.Offset(0, 2) = f6

End Sub

I have figure out that i can not insurt a string into a cell that start with
"=". I do not know how to get around this. any ideas?

"Luke M" wrote:

Perhaps change the last line to:
ActiveCell.Offset(0, 2).Formula = f6
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"cluckers" wrote:

I am trying to use the Reuters quote function in a macro. I have created a
string that gets the symbols and date from cells of the excel file. I can
successfully have the strinf displayed in a message box to show that I have
successfully created the function for reuters. However when I attempt to
have this inputed into a cell I get the runtime 1004 error. can you help?
thanks

Sub valuation()

Dim tdate As String
Dim symbol As String
Dim price As String
Dim valuation As String
Dim f1 As String
Dim f2 As String
Dim f3 As String
Dim f4 As String
Dim f5 As String
Dim f6 As String

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

valuation = ActiveCell.Offset(0, 3)

f2 = ",,"
f3 = ","
f4 = "close"
f5 = Chr(34)

f1 = "=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

f6 = f1 & f2 & symbol & f3 & f5 & f4 & f5 & f3 & tdate & f3 & f5 & f5

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

MsgBox (f6)

ActiveCell.Offset(0, 2) = f6

End Sub

Your formula is wrong. It's missing at least a closing paren.

When I'm having trouble building the formula, I'll make the formula a string:

f1 = "$=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

Then I can go back to the cell with this value and see what's wrong.

I did that with your string and saw this:
$=RTD("ReutersRTD.HystoricalQuote" ,,,"close",,""

If it's obvious, I'll just fix the string in the code. If it's not obvious,
I'll try to make it a valid formula in the cell. Then make those same changes
in the code.

(I don't know about the RTD function, so you may have more things wrong than the
closing paren.)

cluckers wrote:

I am trying to use the Reuters quote function in a macro. I have created a
string that gets the symbols and date from cells of the excel file. I can
successfully have the strinf displayed in a message box to show that I have
successfully created the function for reuters. However when I attempt to
have this inputed into a cell I get the runtime 1004 error. can you help?
thanks

Sub valuation()

Dim tdate As String
Dim symbol As String
Dim price As String
Dim valuation As String
Dim f1 As String
Dim f2 As String
Dim f3 As String
Dim f4 As String
Dim f5 As String
Dim f6 As String

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

valuation = ActiveCell.Offset(0, 3)

f2 = ",,"
f3 = ","
f4 = "close"
f5 = Chr(34)

f1 = "=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

f6 = f1 & f2 & symbol & f3 & f5 & f4 & f5 & f3 & tdate & f3 & f5 & f5

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

MsgBox (f6)

ActiveCell.Offset(0, 2) = f6

End Sub

--

Dave Peterson

The problem is it does not allow the function to be inserted. you get the
run time 1004 error

"Dave Peterson" wrote:

Your formula is wrong. It's missing at least a closing paren.

When I'm having trouble building the formula, I'll make the formula a string:

f1 = "$=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

Then I can go back to the cell with this value and see what's wrong.

I did that with your string and saw this:
$=RTD("ReutersRTD.HystoricalQuote" ,,,"close",,""

If it's obvious, I'll just fix the string in the code. If it's not obvious,
I'll try to make it a valid formula in the cell. Then make those same changes
in the code.

(I don't know about the RTD function, so you may have more things wrong than the
closing paren.)

cluckers wrote:

I am trying to use the Reuters quote function in a macro. I have created a
string that gets the symbols and date from cells of the excel file. I can
successfully have the strinf displayed in a message box to show that I have
successfully created the function for reuters. However when I attempt to
have this inputed into a cell I get the runtime 1004 error. can you help?
thanks

Sub valuation()

Dim tdate As String
Dim symbol As String
Dim price As String
Dim valuation As String
Dim f1 As String
Dim f2 As String
Dim f3 As String
Dim f4 As String
Dim f5 As String
Dim f6 As String

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

valuation = ActiveCell.Offset(0, 3)

f2 = ",,"
f3 = ","
f4 = "close"
f5 = Chr(34)

f1 = "=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

f6 = f1 & f2 & symbol & f3 & f5 & f4 & f5 & f3 & tdate & f3 & f5 & f5

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

MsgBox (f6)

ActiveCell.Offset(0, 2) = f6

End Sub

--

Dave Peterson
.

Your string that creates the function is not valid. You have to fix that
string.

Did you try adding the closing parenthesis?

Did you try building the function manually inside excel (not via VBA)?

What did that working formula look like when you compared it to the string you
were building in code?

cluckers wrote:

The problem is it does not allow the function to be inserted. you get the
run time 1004 error

"Dave Peterson" wrote:

Your formula is wrong. It's missing at least a closing paren.

When I'm having trouble building the formula, I'll make the formula a string:

f1 = "$=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

Then I can go back to the cell with this value and see what's wrong.

I did that with your string and saw this:
$=RTD("ReutersRTD.HystoricalQuote" ,,,"close",,""

If it's obvious, I'll just fix the string in the code. If it's not obvious,
I'll try to make it a valid formula in the cell. Then make those same changes
in the code.

(I don't know about the RTD function, so you may have more things wrong than the
closing paren.)

cluckers wrote:

I am trying to use the Reuters quote function in a macro. I have created a
string that gets the symbols and date from cells of the excel file. I can
successfully have the strinf displayed in a message box to show that I have
successfully created the function for reuters. However when I attempt to
have this inputed into a cell I get the runtime 1004 error. can you help?
thanks

Sub valuation()

Dim tdate As String
Dim symbol As String
Dim price As String
Dim valuation As String
Dim f1 As String
Dim f2 As String
Dim f3 As String
Dim f4 As String
Dim f5 As String
Dim f6 As String

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

valuation = ActiveCell.Offset(0, 3)

f2 = ",,"
f3 = ","
f4 = "close"
f5 = Chr(34)

f1 = "=RTD(" & f5 & "ReutersRTD.HystoricalQuote"" "

f6 = f1 & f2 & symbol & f3 & f5 & f4 & f5 & f3 & tdate & f3 & f5 & f5

Range("A2").Activate
tdate = ActiveCell
symbol = ActiveCell.Offset(0, 1)

MsgBox (f6)

ActiveCell.Offset(0, 2) = f6

End Sub

--

Dave Peterson
.

--

Dave Peterson