 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2006saving data from a formula into a variable
hi all, does anyone know how to save data coming form formulaarray directly to a variable? How about you getting your formula to work in the worksheet? Then copy|paste that working formula into your reply. It'll be easier to modify that than to start from scratch. > hi all, > does anyone know how to save data coming form formulaarray directly to > a variable?  SignatureDave Peterson hi dave this is the formula: "=INDEX('[somefile.xls]" & WsName & "'!R2C6:R10000C6,MATCH(1,(VALUE(RC[-10])='[somefile.xls]" & WsName & "'!R2C1:R10000C1)*("" & gradegroup & ""='[Job somefile.xls]" & WsName & "'!R2C4:R10000C4),0))" im putting it inside of arrayformula function by the way do you know how can i add to the formula in vba this sign "" with a variable i mean for example: you have this formula vlooup("fish",.......) so if i have the fish in a variable i want to have it in the formula vlookup(" & myvariable &" so how can i do it,it works only when there is no need for "")inverted commas ) Dave Peterson : > How about you getting your formula to work in the worksheet? Then copy|paste > that working formula into your reply. It'll be easier to modify that than to [quoted text clipped - 3 lines] > > does anyone know how to save data coming form formulaarray directly to > > a variable? You can double up the double quotation marks: Dim myFormula As String myformula = "=vlookup(""fish"", ...... or you can use chr(34) myformula = "=vlookup(" & chr(34) & "fish" & chr(34) & ",.... > hi dave > this is the formula: [quoted text clipped - 31 lines] > > > > Dave Peterson SignatureDave Peterson haven't we been here before? SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > hi all, > does anyone know how to save data coming form formulaarray directly to > a variable? hi bob, i dont know why but when i'm using this formula on the excel manualy its ok but when i'm using it via vba its not working: xlApp.Range("S" & row).FormulaArray = "=INDEX(xlFile & "'!R2C6:R10000C6,MATCH(1,and((R[-1]C[1]<=RIGHT(xlFile & "'!R2C4:R10000C4,3)),(R[-1]C[1]>=LEFT(xlFile & "'!R2C4:R10000C4,3))),0))" its raising the error 1004:"unable to set formulaArray property of range class" what can be wrong here? > haven't we been here before? > [quoted text clipped - 8 lines] > > does anyone know how to save data coming form formulaarray directly to > > a variable? Quite a few errors in there sFormula = "=INDEX('" & xlFile & _ "'!R2C6:R10000C6,MATCH(1,(R[-1]C[1]<=RIGHT('" & xlFile & _ "'!R2C4:R10000C4,3))*(R[-1]C[1]>=LEFT('" & xlFile & _ "'!R2C4:R10000C4,3)),0))" Range("S" & Row).FormulaArray = sFormula SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > hi bob, > i dont know why but when i'm using this formula on the excel manualy [quoted text clipped - 21 lines] > > > does anyone know how to save data coming form formulaarray directly to > > > a variable? hi bob, i dont thing its the xlfile issue becaouse its just the implementation of what suppose to be there,so i think its a diffrent problem, when i'm trying to record the formula to a maco,i'm getting msgbox that i cannot record the formula. are you sure that there isnt any bugs in this option,maybe i found some problem that not supposed to be append? > Quite a few errors in there > [quoted text clipped - 36 lines] > > > > does anyone know how to save data coming form formulaarray directly to > > > > a variable? Well yours didn't work for me, that one did. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > hi bob, > i dont thing its the xlfile issue becaouse its just the implementation [quoted text clipped - 44 lines] > > > > > does anyone know how to save data coming form formulaarray directly to > > > > > a variable? hi bob, i didnt say something regarding the suggestion you gave me,thats not the problem, the problem is that how it can be be the excel accepts some formula and the vba is not accepting? > Well yours didn't work for me, that one did. > [quoted text clipped - 54 lines] > directly to > > > > > > a variable? hi bob, i found an article regarding this metter please see below,i will be very thankfull if you have a solution for this: The information in this article applies to: Microsoft Excel 2000 ---------------------------------------------------------------------------- ---- SYMPTOMS When you try to create an array formula by using a Microsoft Visual Basic for Applications macro, you may receive the following error message: Run-time error '1004': Unable to set the FormulaArray property of the Range class CAUSE This problem occurs when you try to pass a formula that contains more than 255 characters, and you are using the FormulaArray property in Visual Basic for Applications. RESOLUTION Do not pass formulas that contain over 255 characters to a FormulaArray in Visual Basic for Applications. STATUS Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. MORE INFORMATION An array formula can perform multiple calculations and then returns either a single result or multiple results. You create array formulas the same way that you create basic, single-value formulas. However, with an array formula, after you create the formula, instead of entering the formula by pressing ENTER, you enter the formula by pressing CTRL+SHIFT+ENTER. You can identify an array formula by the braces ({}) surrounding the formula. To create an array formula in Visual Basic for Applications, you can use the FormulaArray property. However, you cannot create a formula that has more than 255 characters by using this property. Note that the Formula property in Visual Basic for Applications does not have this limit. REFERENCES For more information about the FormulaArray property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type FormulaArray Property in the Office Assistant or the Answer Wizard, and then click Search to view the topic. For additional information, please see the following articles in the Microsoft Knowledge Base: Q213841 XL: Passed Strings Longer Than 255 Characters Are Truncated Q212172 XL2000: "Unable to Record" Message When Recording a Macro Additional query words: OFF2000 XL2000 Keywords : kberrmsg kbprg kbdta xlformula Version : WINDOWS:2000 Platform : WINDOWS Issue type : kbbug Technology : --------------------------------------------------------------------------- > hi bob, > i didnt say something regarding the suggestion you gave me,thats not [quoted text clipped - 59 lines] > > directly to > > > > > > > a variable? Your formula, or at least the one you posted, doesn't contain more than 255 chars, so it can't be that. Can you post a workbook somewhere, one of the web file servers? SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) hi bob, i found an article regarding this metter please see below,i will be very thankfull if you have a solution for this: The information in this article applies to: Microsoft Excel 2000 --------------------------------------------------------------------------- - ---- SYMPTOMS When you try to create an array formula by using a Microsoft Visual Basic for Applications macro, you may receive the following error message: Run-time error '1004': Unable to set the FormulaArray property of the Range class CAUSE This problem occurs when you try to pass a formula that contains more than 255 characters, and you are using the FormulaArray property in Visual Basic for Applications. RESOLUTION Do not pass formulas that contain over 255 characters to a FormulaArray in Visual Basic for Applications. STATUS Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. MORE INFORMATION An array formula can perform multiple calculations and then returns either a single result or multiple results. You create array formulas the same way that you create basic, single-value formulas. However, with an array formula, after you create the formula, instead of entering the formula by pressing ENTER, you enter the formula by pressing CTRL+SHIFT+ENTER. You can identify an array formula by the braces ({}) surrounding the formula. To create an array formula in Visual Basic for Applications, you can use the FormulaArray property. However, you cannot create a formula that has more than 255 characters by using this property. Note that the Formula property in Visual Basic for Applications does not have this limit. REFERENCES For more information about the FormulaArray property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type FormulaArray Property in the Office Assistant or the Answer Wizard, and then click Search to view the topic. For additional information, please see the following articles in the Microsoft Knowledge Base: Q213841 XL: Passed Strings Longer Than 255 Characters Are Truncated Q212172 XL2000: "Unable to Record" Message When Recording a Macro Additional query words: OFF2000 XL2000 Keywords : kberrmsg kbprg kbdta xlformula Version : WINDOWS:2000 Platform : WINDOWS Issue type : kbbug Technology : --------------------------------------------------------------------------- > hi bob, > i didnt say something regarding the suggestion you gave me,thats not [quoted text clipped - 68 lines] > > directly to > > > > > > > a variable?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.