A faster/more efficient way?

[braven]

Hello all,

I'm working on a little (100mb) spreadsheet that pulls from a lot of different data sources to consolidate a lot of different things together. Standard stuff. I try to make things run as efficiently as possible.. using autofilter when I can instead of loops, that sort of thing. I've fairly OK with VBA, but not really an expert.

The longest process in my daily consolidate task is the following bit of code that inserts a INDEX/MATCH/MATCH formula into a large cell range. The reason I am inserting this via code is that that prior to this bit of code, the whole worksheet is dynamically created.

My question: Is there a more efficient way to set a formula on a large range of cells but yet still have the ability to reference the row number of the ActiveCell as well as the Column number (or letter) of the ActiveCell for inserting into my formulas? Or should I just consign myself to getting a cup of coffee every time this runs?

Many thanks for many helpful replies!


[vba]
For Each cell In Worksheets("Technical Input").Range("C3:Z1500").Cells
myRow = cell.Row
myCol = Number2Char(cell.Column) ' custom function
Range("A12").Value = myCol & myRow ' i use this as my progress indicator
match1 = "MATCH($A" & myRow & ",'Technical Data'!$BN$18:$BN$10000, 0)"
match2 = "MATCH(" & myCol & "$1,'Technical Data'!$BN$17:$ZZ$17, 0)"
cell.Formula = "=IFERROR(INDEX('Technical Data'!$BN$18:$ZZ$10000," & match1 & ", " & match2 & "), """")"
Next
[/vba]

[mdmackillop]

If you use FormulaR1C1 you can use relative and fixed references
eg
[VBA]
Range("A1").FormulaR1C1 = "=SUM(RC[1]:RC[5])"
Range("A2").FormulaR1C1 = "=SUM(R1C2:RC[4])"
[/VBA]
You can then fill the whole range in one line
[VBA] Range("A1:A50").FormulaR1C1 = "=SUM(RC[1]:RC[5])"
[/VBA]

[p45cal]

I'd like to hope you only need this one-liner:
[vba]Worksheets("Technical Input").Range("C3:Z1500").FormulaR1C1 = _
"=IFERROR(INDEX('Technical Data'!R18C66:R10000C702,MATCH(RC1,'Technical Data'!R18C66:R10000C66, 0), MATCH(R1C,'Technical Data'!R17C66:R17C702, 0)), """")"
[/vba]no progress monitor required, no function converting numbers to letters.

ps. sorry to spoil your coffe breaks!

[braven]

I'd like to hope you only need this one-liner:
[vba]Worksheets("Technical Input").Range("C3:Z1500").FormulaR1C1 = _
"=IFERROR(INDEX('Technical Data'!R18C66:R10000C702,MATCH(RC1,'Technical Data'!R18C66:R10000C66, 0), MATCH(R1C,'Technical Data'!R17C66:R17C702, 0)), """")"
[/vba]no progress monitor required, no function converting numbers to letters.

ps. sorry to spoil your coffe breaks!

This.. this brings a tear to my eye.. it is a work of such beauty!

And for the coffee breaks, who says I won't still tell the boss it takes 10 minutes to run??

Thank you very much!

I now have something to dig deeper into.. FormulaR1C1 looks seriously powerful.