Hi
Lol!!!!
I tested your code p45cal and got it to work. Sorry I have now cleaned up both pieces of code as there was bunch of stuff I was experimenting with but it should make sense now. They return identical results. However my code takes 10.9 seconds to run (Version 2) versus 8.8 for the other (Version1) So it seems using the index function makes it run slightly slower. Not a huge time difference here but I only tested it on a small dataset, the difference could be much bigger on a larger one. So it seems Version1 maybe the best way to go. How would I modify Version1 to only call the custom function once instead of 4 times?
One other question is would reading all the data from Sheet ("X Variables") in one go into a big data array and than iterating each col of data from that array into the UDF (instead of copying and pasting one at a time in column U as currently) speed or slow down the macro? I'm assuming it would speed it up as Excel would only have to read the raw data in once and don't need to copy and paste multiple times but then again maybe reading in a big array into memory may have the opposite effect?
Sub Version1()
StartTime = Timer
Dim Startrange As Long, ColNum As Long, lastrow As Long
Dim outvariables As Integer
Dim rCalc As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lastrow = Range("B15").End(xlDown).row
lastcol = Range("R14").End(xlToLeft).Column 'address bring back the cell address, splits on takes the letter from address
Startrange = 15
ColNum = Range("R14").End(xlToLeft).Column - 1 'determines start
outvariables = ColNum - 2 'the number of variables
lastvar = Split(Range("R14").End(xlToLeft).Address, "$")(1) 'address bring back the cell address, splits on takes the letter from address
Sheets("X Variables").Select
z = Sheets("X Variables").Range("C1").End(xlToRight).Column
For i = 4 To z 'assumes x variables begins with column 3
Sheets("Calculation").Range("U14:U" & lastrow).Value = Sheets("X Variables").Range(Cells(1, i), Cells(lastrow, i)).Value
Set rCalc = Sheets("Calculation").Range("U" & Startrange & ":U" & lastrow)
Dim Results(4 To 33, 1 To 4) As Variant
'for each column of data/series
Results(i, 1) = Sheets("Calculation").Range("U14").Value
For j = 2 To 4
Results(i, j) = UDF(rCalc, 1)(1, j - 1)
Next j
Next i
Sheets("Calculation").Select
Sheets("Calculation").Range("AY2:BB40") = Results()
'Stop
Application.ScreenUpdating = True
MsgBox Timer - StartTime & " seconds"
End Sub
Sub Version2()
StartTime = Timer
Dim Startrange As Long, ColNum As Long, lastrow As Long
Dim outvariables As Integer
Dim rCalc As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lastrow = Range("B15").End(xlDown).row
lastcol = Range("R14").End(xlToLeft).Column 'address bring back the cell address, splits on takes the letter from address
Startrange = 15
ColNum = Range("R14").End(xlToLeft).Column - 1 'determines start
outvariables = ColNum - 2 'the number of variables
lastvar = Split(Range("R14").End(xlToLeft).Address, "$")(1) 'address bring back the cell address, splits on takes the letter from address
Sheets("X Variables").Select
z = Sheets("X Variables").Range("C1").End(xlToRight).Column
For i = 4 To z 'assumes x variables begins with column 3
Sheets("Calculation").Range("U14:U" & lastrow).Value = Sheets("X Variables").Range(Cells(1, i), Cells(lastrow, i)).Value
Set rCalc = Sheets("Calculation").Range("U" & Startrange & ":U" & lastrow)
Dim Results(4 To 33, 1 To 4) As Variant
Results(i, 1) = Sheets("Calculation").Range("U14").Value
Results(i, 2) = Application.Index(UDF(rCalc, 1), 1, 1)
Results(i, 3) = Application.Index(UDF(rCalc, 1), 1, 2)
Results(i, 4) = Application.Index(UDF(rCalc, 1), 1, 3)
Results(i, 5) = Application.Index(UDF(rCalc, 1), 1, 4)
Sheets("Calculation").Select
Sheets("Calculation").Range("AY2:BB40") = Results()
'Stop
Application.ScreenUpdating = True
MsgBox Timer - StartTime & " seconds"
End Sub