PDA

View Full Version : VBA: Loop a simple calculation through a 2 dimensional array



Silverjman
01-18-2019, 03:33 AM
The below is what I am trying to fully recreatein VBA, except that I have 40 columns and 4,500 rows and loads more gridssimilar to this. This is all currently in an Excel model that takes 90 secondsto recalc (no volatile formulas).

=(B$1=$P2)*1



1 2 3 4 5
Asset_0001 =(B$1=$P2)*1 =(C$1=$P2)*1 =(D$1=$P2)*1 =(E$1=$P2)*1 =(F$1=$P2)*1
Asset_0002 =(B$1=$P3)*1 =(C$1=$P3)*1 =(D$1=$P3)*1 =(E$1=$P3)*1 =(F$1=$P3)*1
Asset_0003 =(B$1=$P4)*1 =(C$1=$P4)*1 =(D$1=$P4)*1 =(E$1=$P4)*1 =(F$1=$P4)*1
Asset_0004 =(B$1=$P5)*1 =(C$1=$P5)*1 =(D$1=$P5)*1 =(E$1=$P5)*1 =(F$1=$P5)*1
Asset_0005 =(B$1=$P6)*1 =(C$1=$P6)*1 =(D$1=$P6)*1 =(E$1=$P6)*1 =(F$1=$P6)*1
Asset_0006 =(B$1=$P7)*1 =(C$1=$P7)*1 =(D$1=$P7)*1 =(E$1=$P7)*1 =(F$1=$P7)*1
Asset_0007 =(B$1=$P8)*1 =(C$1=$P8)*1 =(D$1=$P8)*1 =(E$1=$P8)*1 =(F$1=$P8)*1
Asset_0008 =(B$1=$P9)*1 =(C$1=$P9)*1 =(D$1=$P9)*1 =(E$1=$P9)*1 =(F$1=$P9)*1
Asset_0009 =(B$1=$P10)*1 =(C$1=$P10)*1 =(D$1=$P10)*1 =(E$1=$P10)*1 =(F$1=$P10)*1
Asset_0010 =(B$1=$P11)*1 =(C$1=$P11)*1 =(D$1=$P11)*1 =(E$1=$P11)*1 =(F$1=$P11)*1


I realize the below is pathetic, but it's all Ihave after hours of googling and a Udemy course on VBA arrays (yes make jokes)I would by any court or book but I must not know what I'm looking for because Ican't seem to find the wording that covers what I want to do.

Sub AssetRecovered()
'Dim Quarters1to40 As Variant
'Dim expensesTo As Variant
Dim AssetRecovered(1 To 10, 1 To 40)

xArr = Range("Quarters_1to40")
yArr = Range("expenses_To")

CalcARFormula = Asset_Recovered_Formula(AssetRecovered, xArr, yArr)

End Sub

Function Asset_Recovered_Formula(AssetRecovered, xArr, yArr)
For x = LBound(AssetRecovered) To UBound(AssetRecovered)
AssetRecovered(x) = xArrx(x) * yArr(x)
Next x
End Function
OK, any and all help or pointers areappreciated, I do feel stupid asking but am at my wits end.

Paul_Hossler
01-18-2019, 09:30 AM
I think a small sample workbook and a clear description with results of a 'before and after' or 'inputs to outputs' would be helpful