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.
=(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.