Problems usinga a variable in a worksheet function?

[Kobayashi]

Frank,

Thans very much for replying.

Most of the details are in the previous posts but, here is the crux o
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(),""Curren
Item"")"

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

I was kindly offered the following by others on this site but thes
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 an
dumb oversight on my behalf?

Any help would really be appreciated.

Thanks,

Adria

 

[DMoney]

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.php?threadid=276260

.