Y
yaniv.dg
hi all,
does anyone know how to save data coming form formulaarray directly to
a variable?
does anyone know how to save data coming form formulaarray directly to
a variable?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
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
start from scratch.
that working formula into your reply. It'll be easier to modify that than to
start from scratch.
B
Bob Phillips
haven't we been here before?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
Y
yaniv.dg
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 כתב:
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 כתב:
D
Dave Peterson
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) & ",....
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:
"=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
start from scratch.
Y
yaniv.dg
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?
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?
B
Bob Phillips
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
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
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
--
HTH
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
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?
Bob said:haven't we been here before?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
Y
yaniv.dg
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?
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?
Bob said: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
--
HTH
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
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?
Bob said:haven't we been here before?
--
HTH
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?
B
Bob Phillips
Well yours didn't work for me, that one did.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
--
HTH
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
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?
Bob said: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
--
HTH
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
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?
Bob Phillips wrote:
haven't we been here before?
--
HTH
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?
Y
yaniv.dg
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?
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?
Bob said:Well yours didn't work for me, that one did.
--
HTH
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
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?
Bob said: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
--
HTH
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
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?
Bob Phillips wrote:
haven't we been here before?
--
HTH
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?
Y
yaniv.dg
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 :
---------------------------------------------------------------------------
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
the problem,
the problem is that how it can be be the excel accepts some formula and
the vba is not accepting?
Bob said:Well yours didn't work for me, that one did.
--
HTH
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
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?
Bob Phillips wrote:
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
--
HTH
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
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?
Bob Phillips wrote:
haven't we been here before?
--
HTH
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?
B
Bob Phillips
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?
--
HTH
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 :
---------------------------------------------------------------------------
chars, so it can't be that.
Can you post a workbook somewhere, one of the web file servers?
--
HTH
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
the problem,
the problem is that how it can be be the excel accepts some formula and
the vba is not accepting?
Bob said:Well yours didn't work for me, that one did.
--
HTH
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
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?
Bob Phillips wrote:
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
--
HTH
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
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?
Bob Phillips wrote:
haven't we been here before?
--
HTH
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?