InputBox value in formula problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone tell me what the problem is with the following code?
Period = InputBox("Input invoice period, e.g. AUG-07", "Period")
Sheets("ANNUAL MASTER").Select
Range("D7").Select
ActiveCell.FormulaR1C1 = "=Spreadsheet!R[-5]C[-3] &"" """ & Period

When I run this in a Macro, I get a VB "Run-time error '1004':
Application-defined or object-defined error". It doesn't seem to like the "&
Period" portion at the end of the last line. If I take that piece out, it
works fine. The code seems to conform to the syntax that I have seen in many
other places, so I'm stumped.

Any help would be greatly appreciated!
 
you have issues with your & and your quotes... Give this a try...

Period = InputBox("Input invoice period, e.g. AUG-07", "Period")
Sheets("ANNUAL MASTER").Select
Range("D7").Formula = "=Spreadsheet!A2 & " & " " & Period
 
Thanks Jim, that did the trick! Thanks very much for your quick help!

Jim Thomlinson said:
you have issues with your & and your quotes... Give this a try...

Period = InputBox("Input invoice period, e.g. AUG-07", "Period")
Sheets("ANNUAL MASTER").Select
Range("D7").Formula = "=Spreadsheet!A2 & " & " " & Period

--
HTH...

Jim Thomlinson


COV MarshallT said:
Can anyone tell me what the problem is with the following code?
Period = InputBox("Input invoice period, e.g. AUG-07", "Period")
Sheets("ANNUAL MASTER").Select
Range("D7").Select
ActiveCell.FormulaR1C1 = "=Spreadsheet!R[-5]C[-3] &"" """ & Period

When I run this in a Macro, I get a VB "Run-time error '1004':
Application-defined or object-defined error". It doesn't seem to like the "&
Period" portion at the end of the last line. If I take that piece out, it
works fine. The code seems to conform to the syntax that I have seen in many
other places, so I'm stumped.

Any help would be greatly appreciated!
Click to expand...
Click to expand...
 
Can anyone tell me what the problem is with the following code?
Period = InputBox("Input invoice period, e.g. AUG-07", "Period")
Sheets("ANNUAL MASTER").Select
Range("D7").Select
ActiveCell.FormulaR1C1 = "=Spreadsheet!R[-5]C[-3] &"" """ & Period
Click to expand...

Assuming "Spreadsheet" reference is correct, try it this way...

ActiveCell.FormulaR1C1 = "=Spreadsheet!R[-5]C[-3]&""" & " " & Period & """"

The other possibility is to modify the Period assignment line like this....

Period = " " & InputBox("Input invoice period, e.g. AUG-07", "Period")

and then use this for the last line...

ActiveCell.FormulaR1C1 = "=Spreadsheet!R[-5]C[-3]&""" & Period & """"

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup VBA on dates 2
Run-time error '1004': Application defined or Object defined error 2
Formula in given via input box 5
Trying to return Range from InputBox 3
Using VBA to Insert a formula in a cell 2
Inputbox/Regression Problem 2
Problem with formulae in VBA 2
Populating formulas in a spreadSheet with VBA 2

Back
Top