Try adding a string declaration and assigning it the name
of the workbook then use this in the function.

Dim OCB As Workbook
Dim OCS As Worksheet
Set OCB = ActiveWorkbook - (activeworkbook is called
1709cds.xls)
Set OCS = OCB.ActiveSheet - (activesheet is called
1709cds)
Dim OCC As String
OCC = OCB.Name

CRMatchRange.FormulaR1C1 =
"=IF(ISNA(MATCH(RC[6],'" & OCC & "'!R2C1:R1421C1,0)),
today(),""Current
Item"")"





-----Original Message-----

Frank,

Thans very much for replying.

Most of the details are in the previous posts but, here
is the crux of
the matter:

I have declared and set the following:

Dim OCB As Workbook
Dim OCS As Worksheet
Set OCB = ActiveWorkbook - (activeworkbook is called
1709cds.xls)
Set OCS = OCB.ActiveSheet - (activesheet is called
1709cds)

Further on in the code I am trying to use the following
function:

CRMatchRange.FormulaR1C1 =
"=IF(ISNA(MATCH(RC[6],'1709cds.xls'!R2C1:R1421C1,0)),
today(),""Current
Item"")"

In this format it works just as I'd like but I want to
change the
hardcoded workbook reference to a variable (hopefully
either OCB or
OCS). However this is where it falls over and I just
can't get it to
work!

I was kindly offered the following by others on this site
but these
don't work either:

CRMatchRange.FormulaR1C1 = "=IF(ISNA(MATCH(RC[6],'" & OCB
&
"'!R2C1:R1421C1,0)), today()," & Chr(34) & "Current Item"
& Chr(34) &
")"

I'm pulling my hair out for what, I thought, would be an
easy fix and
dumb oversight on my behalf?

Any help would really be appreciated.

Thanks,

Adrian


--
Kobayashi
----------------------------------------------------------
--------------
Kobayashi's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=871
View this thread:
http://www.excelforum.com/showthread...hreadid=276260

.