Hello there. It's been awhile since I programmed in VBA, but as my job at the University has me working with some pretty big spreadsheets I am heading into VB to make my own UDF. Here is what I have so far:
Function NewLook(j As Integer, k As Integer)
'Initialize the Array
Dim aSize As Integer
aSize = 4200 - j
Dim bSize As Integer
bSize = k - 5
Dim a(4200) As Double
Dim b(4200) As Double
For i = aSize To 4200 Step 1
a(i) = Cells(i, 29).value
Next i
For i = 6 To bSize Step 1
b(i) = Cells(i, 14).value
Next i
Dim count As Integer
Dim v As Double
Dim thing As Integer
For c = 1325 To bSize Step 1
thing = c
v = Abs((b(c) - a(j)))
For d = j To 4200 Step 1
If v > Abs((b(c) - a(d))) Then
v = Abs((b(c) - a(d)))
count = d
Debug.Print (count)
End If
Next d
Cells(thing, 43) = count
Next c
You can pretty much ignore everything until the bottom. That is where I will be updating each cell with the value needed. This has to be done somewhere between 2000-4200 times. I tried adding a Sub that the function above calls and does the Cells(thing, 43) = count line but it does not work.
Any suggestions?
Function NewLook(j As Integer, k As Integer)
'Initialize the Array
Dim aSize As Integer
aSize = 4200 - j
Dim bSize As Integer
bSize = k - 5
Dim a(4200) As Double
Dim b(4200) As Double
For i = aSize To 4200 Step 1
a(i) = Cells(i, 29).value
Next i
For i = 6 To bSize Step 1
b(i) = Cells(i, 14).value
Next i
Dim count As Integer
Dim v As Double
Dim thing As Integer
For c = 1325 To bSize Step 1
thing = c
v = Abs((b(c) - a(j)))
For d = j To 4200 Step 1
If v > Abs((b(c) - a(d))) Then
v = Abs((b(c) - a(d)))
count = d
Debug.Print (count)
End If
Next d
Cells(thing, 43) = count
Next c
You can pretty much ignore everything until the bottom. That is where I will be updating each cell with the value needed. This has to be done somewhere between 2000-4200 times. I tried adding a Sub that the function above calls and does the Cells(thing, 43) = count line but it does not work.
Any suggestions?