Use Match or Lookup to create dynamic cell reference in VBA R1C1 formula

**rmunsun1** · 02-17-2011, 05:48 AM

Hey all,

I have a fairly simple nested IF function judging a couple of ISBLANKs as well as some LEN and RIGHT. The problem is that I'd like to put it in VBA. Can do that easily. Here's the catch: I'd like to make the column references dynamic in case the columns get re-arranged. So if "Title 1" gets moved to column C, I'd like the formula to use column C. Does that make sense? Here's the formula in a regular cell.

=IF(ISBLANK(A3),"",IF(ISBLANK(C3),IF(LEN(A3)=13,RIGHT(A3,11),A3),IF(LEN(A3)=13,RIGHT(A3,11)&"-"&C3,A3&"-"&C3)))

The code is easy, but making it so the column can shift is a bit harder. I was trying to include Match("Title 1",$A$1:$Z$1,0) inside, but that doesn't return letters and it gets too complicated for the macro to record.

Any help?

Thank you so much. I appreciate the help.

**brokenbiscuits** · 02-17-2011, 06:07 AM

If the row that "Title 1" would appear on remains static (for instance always would appear on row 3 but the column is variable) you can use

ADDRESS(3,Match("Title 1",$A$1:$Z$1,0))

where 3 is the row number. This would bring back the cell reference i.e. $A$3

Does that help?

**rmunsun1** · 02-17-2011, 06:21 AM

Well the row would remain "static" insomuch as it is the same row that the formula is on. So in VBA, it just an R in an R1C1 formula. So, not R3 or R[-2], but just R. The C part is what I need to be changing.

So in VBA, how would I do, say, the ISBLANK(ADDRESS(R,Match("Title 1",$A$1:$Z$1,0))?

Does this question make any sense?

**DonkeyOte** · 02-17-2011, 07:45 AM

If you can it makes sense to calculate the positions in the sub routine and load as constants into the formula

However, if you need the formula to persist in a dynamic sense forever more then use INDEX (ADDRESS warrants Volatile INDIRECT wrapper)

On that basis:

=IF(ISBLANK($A3),"",IF(LEN($A3)=13,RIGHT($A3,11),$A3)&IF(INDEX(3:3,MATCH("Title 1",$1:$1,0))<>"","-"&INDEX(3:3,MATCH("Title 1",$1:$1,0)),""))

In VBA R1C1 form

.FormulaR1C1 = "=IF(ISBLANK(RC1),"""",IF(LEN(RC1)=13,RIGHT(RC1,11),RC1)&IF(INDEX(R,MATCH(""Title 1"",R1,0))<>"""",""-""&INDEX(R,MATCH(""Title 1"",R1,0)),""""))"

**rmunsun1** · 02-17-2011, 08:46 AM

Originally Posted by DonkeyOte

If you can it makes sense to calculate the positions in the sub routine and load as constants into the formula

I like the sounds of this. Are you saying something like:

ColumnMatch1 = Match("Title 1", $A1,$Z$1,0)?

How would that fit into the equation I'm looking at?

IF(ISBLANK(Cells(R,ColumnMatch1))...?

Something along those lines?

I'm focusing on the R1C1 situation just so I can leave in an "R" and be able to fill down with the rows updating to the same row as formula.

**DonkeyOte** · 02-17-2011, 08:52 AM

Along the lines of:

Dim vMatch As Variant
With Sheets("Sheet1")
    vMatch = Application.Match("Title 1", .Rows(1), 0)
    If IsNumeric(vMatch) Then
        .Range("D2:D10").FormulaR1C1 = "=IF(ISBLANK(RC1),"""",IF(LEN(RC1)=13,RIGHT(RC1,11),RC1)&IF(RC" & vMatch & "<>"""",""-""&RC" & vMatch & ",""""))"
    End If
End With

use of Sheet1 & D2:D10 purely hypothetical

also you don't have to use R1C1 you can use A1 (formula) if preferred you just need to ensure you use mixed references correctly (and adjust vMatch approach obviously)

**rmunsun1** · 02-17-2011, 10:24 AM

This is awesome. Thank you so much DonkeyOte. I never can get all the syntax right in regards to "s and &s, not to mention separating out Applications like that.

A lot to learn.

Thanks for another lesson. I am very grateful.