Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Converting an array formula to VBA

I understand ithat it is not possible to use an array formula in VBA using
the " .formula = " context. Can someone show me the steps, and/or the logic
for creating an array formula in VBA? I don't want these 30 array formulas
to stay in the worksheet, since I copy this sheet 60-120 times within a
workbook. Although (if possible) I could copy the formula in, paste the
values, and then delete the formulas, I would rather just pass the values to
the proper cells. The formula uses two criteria to find a value in a table
on a separate sheet within the workbook:

{=IF(ISERROR(INDEX(RETAIL,MATCH(Z7&T7,CONCAT&RETAI L,0))),INDEX(RETAIL,MATCH(Z7,CONCAT,0)),INDEX(RETA IL,MATCH(Z7&T7,CONCAT&RETAIL,0)))}

"RETAIL" is a range (one column) containing retail prices
"CONCAT" is another one column range in the same table which concatenates a
product code

The sheet that uses this formula (on 32 lines) is replicated 60-120 times
within the workbook, which slows everything down and creates bloat. I've
converted the rest of my formulas to VBA, but this one is hard for me to
understand. Any ideas would be greatly appreciated!
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 129
Default Converting an array formula to VBA


YourRange.FormulaArray =

PV Jefe wrote:
I understand ithat it is not possible to use an array formula in VBA using
the " .formula = " context. Can someone show me the steps, and/or the logic
for creating an array formula in VBA? I don't want these 30 array formulas
to stay in the worksheet, since I copy this sheet 60-120 times within a
workbook. Although (if possible) I could copy the formula in, paste the
values, and then delete the formulas, I would rather just pass the values to
the proper cells. The formula uses two criteria to find a value in a table
on a separate sheet within the workbook:

{=IF(ISERROR(INDEX(RETAIL,MATCH(Z7&T7,CONCAT&RETAI L,0))),INDEX(RETAIL,MATCH(Z7,CONCAT,0)),INDEX(RETA IL,MATCH(Z7&T7,CONCAT&RETAIL,0)))}

"RETAIL" is a range (one column) containing retail prices
"CONCAT" is another one column range in the same table which concatenates a
product code

The sheet that uses this formula (on 32 lines) is replicated 60-120 times
within the workbook, which slows everything down and creates bloat. I've
converted the rest of my formulas to VBA, but this one is hard for me to
understand. Any ideas would be greatly appreciated!


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Converting an array formula to VBA

Muchas Gracias!! That was toooo easy! I think I tend to overcomplicate
things!!

" wrote:


YourRange.FormulaArray =


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting an Array to value's Gunti Excel Discussion (Misc queries) 12 November 3rd 08 02:51 PM
Converting column to array asaylor Excel Worksheet Functions 0 August 7th 06 08:02 PM
Converting a row or column in excel to a Javascript array ncikusa Excel Discussion (Misc queries) 0 April 4th 06 10:04 PM
Converting an array of data to a bitmap JSR Excel Programming 3 May 17th 05 12:19 PM
Problems Converting 1-D Array to 2-D Array ExcelMonkey[_190_] Excel Programming 1 March 28th 05 12:16 AM


All times are GMT +1. The time now is 04:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"