Fill formulas relative to cell with INDIRECT function 1

[CLski]

I have a macro that I currently use to expand a worksheet as needed depending the amount of data input on a previous page. Since the data on the previous page can and will be changed often I need to use an Indirect function (or something similar) in the cell formulas on another page to always link to the same cell address (irreguardless of insert/delete for example).

However at times there can be a large number of rows that the code needs to loop over to set the formulas. The code that I am currently using is rather slow as it has to loop over each cell individually.

Any help as to optimizing the code to fill multiple cells at once similar to

Range("A1","U1").Formula = "..."

versus the current method of

For Each vbCEll In Sheets("MTF Move-Check").Range("C" & Row, "R" & Row)
     vbCEll.Formula = "=IF(INDIRECT(""'Sequence Macro'!" _
         & Chr(Asc(Mid(vbCEll.Address, 2, 1)) - 1) & Row & _
         """)<> """",UPPER(INDIRECT(""'Sequence Macro'!" & _
         Chr(Asc(Mid(vbCEll.Address, 2, 1)) - 1) & Row & _
         """)),"""")"
Next

 

[SkipVought]

Hi,

Seems to me that using MS Query would be light years faster and much less complex. faq68-5829.

maybe even a PivotTable, except that formulas in the sheet could be propogated simpler with MS Query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Alternatively, Name the ranges, using Insert > Name > Create - Create names in TOP row.

Then use INDEX & MATCH to return values.

That would be MUCH less complex than what you're doing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CLski]

I'm just having a little problem implementing the Index and Match combination now though. I don't get the need for the name for the ranges. (I already have unique names for the columns in the second row)

My understanding of what you suggested is the following

Range("C" & Row, "R" & Row).FormulaR1C1 = "=INDEX(Sheet1!C2:R21,Row,MATCH(R[-Row]C,Sheet1!C2:R2,0))"

The only problem I get is obviously in the R1C1 format it can't do the R[-Row]C part of the formula.

 

[SkipVought]

I love to code VBA in Excel. I can do all sorts of fancy stuff.

But I also know and understand the native features of Excel, and oftne use them in lieu of code.

Seems to me that this might be a case like that. Seems that you have a source table that varies in its number of rows. I might use code like this to 'dynamically' create named ranges in the source table, in the Worksheet_Change event...

application.displayalerts = false
with SourceSheetObject.[A1].CurrentRegion.CreateNames True, False, False, False
application.displayalerts = true

Then on the sheet that you're summarizing your data, use the Data > List - Create List feature on your summary table. Each time your add a row, the formulas will 'automatically' propogare to the new row as soon as you begin enterin data. No need for coding a formula! I hardly ever do. Frankly, spreadsheet formulas are a pain to code, I avoid whenever it makes sense. KISS principle!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CLski]

I finally got a more optimized version of the code. I've been avoiding the tables and lists because of the incidental possibility of sorting the information. The way that the information is put in doesn't allow for for sorting it back to the original format (at least that I'm familiar with) and the information has to be in a certain format so the use of a table could cause problems with accidental sorting.

I did finally get the formulas to work in the code. It may not be as fast as tables or lists, but for the number of lines that are likely to be filled at one time the time is only about 1 sec (~400 lines).

What I finally got was

Range("C" & Row).Formula = "=INDEX(Sheet1!$C$2:$R$65536," & Row & ",MATCH(A$2,Sheet1!$C$2:$R$2,0))"
Range("C" & Row).Copy
Range("D" & Row, "R" & Row).PasteSpecial xlPasteFormulas

So maybe not the fastest, but it works, and is much faster than the first version.

 

[SkipVought]

Wow! If sorting poses a problem, you have a real mess, I'd guess, because in database theory, a table is an unordered relation. If your system depends on a certain order, you have a nightmare!

I feel sorry for you. There's gotta be a better way!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

If it's speed your after it may be quicker to read the whole range into an array, do your analysis/sorting within the array and write the output array back to the required range. You will only read and write to excel twice this way