Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems usinga a variable in a worksheet function?
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 -- Kobayash ----------------------------------------------------------------------- Kobayashi's Profile: http://www.excelforum.com/member.php...info&userid=87 View this thread: http://www.excelforum.com/showthread.php?threadid=27626 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems usinga a variable in a worksheet function?
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable named range in worksheet function | Excel Worksheet Functions | |||
VBA Syntax for using a variable in a worksheet function | Excel Discussion (Misc queries) | |||
Problems usinga a variable in a worksheet function? | Excel Programming | |||
Problems usinga a variable in a worksheet function? | Excel Programming | |||
Problems usinga a variable in a worksheet function? | Excel Programming |