Help with using a formula to define the selected range in VBA

[integra27w]

Hello,

I would like to do something like this:
Range("=CELL(""address"",INDEX(INDIRECT(R2C12),3,2))").Select

ActiveCell.FormulaR1C1 = "=CELL(""address"",INDEX(INDIRECT(R2C12),3,2))" will put the formula into the currently active cell, but I can't get the VBA to select a range based off the result of the formula. Please help.

 

[GerryZ]

Hello!
can you post a screen-shot so we can understand better avoiding 1000 of words?
thank you!

 

[integra27w]

The screenshot would be in text. Basically the below VBA is not working. I'll like to make the Range be whatever the result of the formula is. R2C12 will dictate that result based on the month and week range I select.
So R2C12 spits out Jan_1 as the result. Jan_1 is a defined Name Range. Next 3,2 is the column and row within that defined range. So =CELL(""address"",INDEX(INDIRECT(R2C12),3,2))" will result in something like $A$7.
The Macro would then see the formula result as range("A7").select. That's the goal anyway. Hope I explained that well enough. I'm not sure the multiple screenshots would help without words.

Sheets("Sheet1").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Range("=CELL(""address"",INDEX(INDIRECT(R2C12),3,2))").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[mikerickson]

It looks like you have a range address in $L$2 and you want to return the cell that is in row 3, column 2 of that range.
If that is the case,

Range(Range("$L$2").Text).Cells(3, 2).Select

 

[integra27w]

Yes that is exactly the Idea. I tried the code you provided and it did not work.

 

[mikerickson]

What do you mean "did not work"? Did it create an error? Which one?
Did it select the wrong cell?

What is in L2? Is there a named range that has exactly that name with no extra spaces?

 

[integra27w]

There is a named range in L2.
It varies between JAN_1 and DEC_10 (Months and 1 thru 10)
If I use the formula: =CELL("address",INDEX(INDIRECT($L$2),3,2)) it returns the cell I need to update. (Just to prove there are no spaces)
The point of this is basically insert a value with a button for that month and that scorecard number.

The error I receive is "Run-time error '1004': Select method of Range class failed"

Here is the current formula:

Sheets("QA").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Range(Range("$L$2").Text).Cells(3, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Here is the value in L2:
JAN_1

The named range for JAN_1 is JanQA!$C$1:$Q$50

 

[integra27w]

***SOLVED***
Mike, I read further about the error message. I got the below code to work. Basically just take Select out of the equation. Thanks for all you're help. I still would not have been able to get this far without the original code you posted!

Sheets("QA").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Range(Range("$L$2").Text).Cells(3, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False