Major Formula Problem

Posted by School Marm on June 11, 2001 2:06 AM

I'm attempting to automate a form and came up with a rather complex formula which I've named "FillAddress":

=ADDRESS(MATCH('Data Input'!$K$3,Teachers,1)+1,COLUMN(CHOOSE('Data Input'!$K$5,'Data Input'!$D$1,'Data Input'!$E$1,'Data Input'!$F$1,'Data Input'!$G$1,'Data Input'!$H$1,'Data Input'!$I$1)),4,TRUE)

Works perfectly so long as I pass it to a cell as text. I have tried passing it to a variable so I can later use it as a Range Object, i.e.

data = "=FillAddress"
Range(" & data & ") = 1
(I have also tried Range("data") = 1
and a number of variations on that theme.)

Anyone out there figure this one out? I'd appreciate it! Thanks in advance.

Posted by School Marm on June 11, 2001 4:09 AM

I report back on my first problem and here's my workaround (only works if the cell ref is single digit):

Dim myString1, myString2, myLookup

myLookup = "=FillAddress"
Cells(4, "K") = myLookup
myLookup = Range("K4")
myString1 = Left(myLookup, 1)
myString2 = Right(myLookup, 1)

Anything more efficient out there?