VBA: Reference cell to use value in formula?

EdNerd

Active Member
Joined
May 19, 2011
Messages
456
I'm trying to create a MATCH formula in VBA, set it in one cell of "Sheet2", and then fill it down a column. The formula will be set in B6, and the Lookup_Value is in C6. The Lookup_Array is 'Sheet1'!A6:A200 - the range A6:A200 in "Sheet1", where "Sheet1" is the text in cell A6 in Sheet2 (where the formula will be set).

The problem I'm running into how to reference Sheet2!A6 in the formula to get the name of Sheet1, so when I fill down the formula, Sheet2!A6 will increment (Sheet2!A7 .... A8 .... etc) and return the cell value, which is the Sheet1 name for the formula.

(I hope that makes sense!!)
Ed
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Won't you just need?

VBA Code:
Worksheets("Sheet2").Range("B6").Formula = "=Match(C6,Sheet1!A$6:A$200,0)"
 
Upvote 0
Sorry for muddling the explanation.
I need to use VBA to create a MATCH formula string and put that in B6.
If I could put it in directly, the MATCH formula would look like:
=MATCH(C6,'54M'!$A$6:$A$2000,0)+5
The problem is the text value of '54M' is in cell A6, and can change.
So the formula in B6 needs to look more like:
=MATCH(C6,'Value of A6'!$A$6:$A$2000,0)+5
That way, when I fill down, the C6 and A6 will increment.
 
Upvote 0
So the trick is how to write the formula bits that create the sheet ref ('54M' in my example) by accessing the text value in Cell A6.
=OFFSET('54M'!B6,0,-1) works by itself. But I can't seem to get that to work inside the whole MATCH formula.
=MATCH(C6,(OFFSET('54M'!B6,0,-1))!$A$6:$A$2000,0)+5 gives me: "There's a problem with this formula."
 
Upvote 0
Try:

ABC
1
2
3
4
5
654M9LookForMe
7
Sheet1
Cell Formulas
RangeFormula
B6B6=MATCH(C6,INDIRECT("'"&A6&"'!$A$1:$A$2000"),)

AB
1
2
3
4
5
6Blah
7Blah
8Blah
9LookForMe
10Blah
11Blah
12Blah
13
54M
 
Upvote 0
Solution

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top