using a macro to move a function into excel cell
 

Deb,

Try this

Worksheets("Reformatted Data").Range("D1").Formula = _
"=INDEX(INDIRECT(""'Downloaded Data'!$A$1:$H$&'Start
Here'!$E$11"")," _
"MATCH(I3,INDIRECT(""'Downloaded Data'!$I$1:$I$&'Start
Here'!$E$11"),0),4)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Deb" wrote in message
...
The function works in Excel. But, when VB encounters the first quote, it
thinks the rest is a comment. I renamed the spreadsheets to have a
one-level name so that I could remove the single quotes, but it still didn't
work. Help!!

Worksheets("Reformatted Data").Range("D1").Formula =
"=INDEX(INDIRECT("'Downloaded Data'!$A$1:$H$"&'Start
Here'!$E$11),MATCH(I3,INDIRECT("'Downloaded Data'!$I$1:$I$"&'Start
Here'!$E$11),0),4)"

using a macro to move a function into excel cell
 

Sorry, I meant

Worksheets("Sheet1").Range("D1").Formula = _
"=INDEX(INDIRECT(""'Downloaded Data'!$A$1:$H$&'Start
Here'!$E$11"")," & _
"MATCH(I3,INDIRECT(""'Downloaded Data'!$I$1:$I$&'Start
Here'!$E$11""),0),4)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Deb,

Try this

Worksheets("Reformatted Data").Range("D1").Formula = _
"=INDEX(INDIRECT(""'Downloaded Data'!$A$1:$H$&'Start
Here'!$E$11"")," _
"MATCH(I3,INDIRECT(""'Downloaded Data'!$I$1:$I$&'Start
Here'!$E$11"),0),4)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Deb" wrote in message
...
The function works in Excel. But, when VB encounters the first quote,
it
thinks the rest is a comment. I renamed the spreadsheets to have a
one-level name so that I could remove the single quotes, but it still
didn't
work. Help!!

Worksheets("Reformatted Data").Range("D1").Formula =
"=INDEX(INDIRECT("'Downloaded Data'!$A$1:$H$"&'Start
Here'!$E$11),MATCH(I3,INDIRECT("'Downloaded Data'!$I$1:$I$"&'Start
Here'!$E$11),0),4)"

using a macro to move a function into excel cell
 

Bob - This worked!! Thank you very much!! Wahoo!