Hi,
Does anyone know how to create an array from a formula without creating a UDF? I am trying this to no avail?
**Also tried putting this in Name Manager and using EVALUATE, strangely getting value error.
Please Login or Register to view this content.
It would help if we had a clue what you're trying to do and what ="{"&Col.Header&"Paid"&"}" is supposed to mean.
Entia non sunt multiplicanda sine necessitate
Do you mean to create an array formula in a cell using VBA? If so, use the FormulaArray property.
Here's an example:
Curly brackets are not needed in the VBA code but will appear in the generated formula.Please Login or Register to view this content.
Last edited by StuCram; 09-09-2015 at 10:58 PM.
If this has been helpful, please click on the star at the left.
An another issue, the ampersand normally should have a space before it when your intent is to concatenate values.
(When an & appears at the end of a variable, it signifies a 'Long' type variable.
Plus, don't forget to include a space (or hyphen, etc.) to improve readability in the generated code
ex
Please Login or Register to view this content.
But as shg suggested, we really need to know much more about your program's intent before answering further.
Please describe in detail what you are trying, including the data both before and after the macro; If possible, attach a sample workbook with your (desensitized) data.
Last edited by StuCram; 09-09-2015 at 11:07 PM.
My apologies, was trying to write an array whoesale using a formula, where Col.Header was a named range. So for instance, where Col.Header gives me the header dynamically, I was hoping to create an array with Col.Header and an appendage and Col.Header by itself.
It doesn't look like there's a way to do it in Excel UI natively, so doesn't look like there's a VBA solution to wrting it into the Name manager.
Sorry for my poor querry. Thanks for your time
Do you want to defined a named range using VBA? That can be done. . .
Try recording an example to see the generated code; then adapt as needed.
Note that RC references in the macro recorder.
Here's what I got . . .
Notes:Please Login or Register to view this content.
1. The Range(. . .).Select instruction is not needed.
2. The RefersToR1C1 parameter may be replaced by the RefersTo parameter with a regular range indication.
example:
Please Login or Register to view this content.
thanks stucram, but no, I was trying to see if I can "write" an array of constants using a formula similar to VBA's Array function. Doesn't look like it though
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks