Excel Issue: Referncing a Range within a formula..

[Chandler_90]

The title to this is a bit vague.. apologies.

I have the following code in Excel ->

Range("G11").Select
ActiveCell.FormulaR1C1 = "=INDEX(SN, MATCH("G18",BL, 0))"

The problem with this is that it interpretates the second " as the end of the formula.

How do I get around this issue?
Its driving me mad.. I've tried ("" "") (' " " ') (' " ' ' " ') ($G$18) (!G18) and other random possibilities.

This is bound to be something stupid..
Wanted to get this finished tonight to show it off to my colleagues 2moro too

 

[Chandler_90]

Why do you need to put quotes in? Excel formulas don't use them?

Anyway to get what I think you want this should work.

ActiveCell.FormulaR1C1 = "=INDEX(SN, MATCH(" & """" & "G18" & """" & ",BL, 0))"

This does indeed work as it allows the code to appear like so in the cell:

=INDEX(SN, MATCH("G18",BL, 0))

However I need this to read:

=INDEX(SN, MATCH(("G18").value,BL, 0))

or something similar yet everything I try creates syntax errors.
Any ideas?

I love this sort of problem.. especially the solving of it as it allows me to learn new techniques..

Yet I can't stand it happenning at the same time lol.

 

[Chandler_90]

ActiveCell.FormulaR1C1 = "=INDEX(SN, MATCH((" & """" & "G18" & """" & ").value,BL, 0))"

I'm sure I tried that.. will go and try again..

Thanks for all your help on this.. as I said before I like things like this.

I must be w31rd

Edit - Nope didn't work.. I'm gonna look in a few books and see if I can find the answer.. Google isn't being my friend at the min!