braven
04-21-2010, 10:37 AM
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!
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
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!
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