I'm trying to use this in my VBA code:
ActiveSheet.Range("A1:A1:).Formula = "='Sheet1'!A1" & Chr(34) & Chr(47) & Chr(34) & "'Sheet1'!A2"
This gives me Error: 1004, Object-defined error.
I'd like to see in the cell formula that:
='Sheet1'!A1"/"'Sheet1'!A2
And if the value of the A1 cell is 10 and the A2 value is 20, the cell value should look like that: 10/20
What can be the problem?
First of all you have a colon instead of a speech mark within Range. You could try something like:
ActiveSheet.Range("A1").Value = Sheet("Sheet1").Range("A1").Value & "/" & Sheet("Sheet1").Range("A2").Value
I'd also recommend not using ActiveSheet as much as possible and also referring to the workbook in references. If you want to refer to the workbook that contains the VBA code you can use ThisWorkbook.
I assume you want the formula to either read:
='Sheet1'!A1&"/"&'Sheet1'!A2 (so: textual concatenation of A1 & A2 with a "/" in between)
or
='Sheet1'!A1/'Sheet1'!A2 (so: the result of dividing A1 by A2)
Check the result of your formula: it generates neither :) (it results in an invalid formula) And, as Jordan answered, your VBA used a colon instead of a double quote.
UPDATE (read over the fact that you asked for textual concatenation in your question, and also adding a solution)
It's best to check the result of your formula build-up while stepping through VBA with the debugger (F8): it generates an invalid formula, on which Excel will no doubt give you an error when you try to set it. The result doesn't contain any & characters to concatenate the values of A1 & A2 with the "/" in between.
The correct formula should be:
='Sheet1'!A1&"/"&'Sheet1'!A2
This is achieved by:
ActiveSheet.Range("A1").Formula = "='Sheet1'!A1&""/""&'Sheet1'!A2"
(note that you can embed a " in a VBA string by doubling it; in this case that is clearer to read than using Chr(34) for the "'s)
