Array/Range lookup

[betherzy]

Hey all, I am newish to VBA and am having a tough time with an easy problem (mostly because I can't communicate what I am looking for well).

I am trying to match a larger column of numbers to a smaller column of numbers so I may interpolate between values.

Ex:

Column A: .. Column B
5 ................ 5
6 ................ 8
7 ................ 12
8
9
10

So the output would interpolate 5,6,7 between 5-8, and 8,9,10 between 8-12

Does this make sense? any help would be appreciated!

[betherzy]

It is important that I loop, because eventually the "Column A" will be about 8000 rows and "Column B" will be about 100 rows.

[betherzy]

Sub interpolate()

Dim MD_array() As Double, TVD() As Double, last_row As Double, last_row3 As Double, TVD_2 As Double
last_row = Range("C3").End(xlDown).Row
last_row3 = Range("F3").End(xlDown).Row
ReDim MD_array(last_row)
ReDim TVD(last_row3)

Dim i As Integer

i = 0#

For i = 0 To last_row
MD_array(i) = Range("C" & i + 3)
Next i

For i = 0 To last_row3
TVD(i) = Range("F" & i + 3)
Next i

Dim depth() As Double, last_row2 As Double, j As Integer
last_row2 = Range("N3").End(xlDown).Row
ReDim depth(last_row2)

For i = 0 To last_row2
depth(i) = Cells(3 + i, 14).Value

For j = 1 To (last_row - 3)
If depth(i) >= MD_array(j - 1) And depth(i) <= MD_array(j) Then
TVD_2 = TVD(j - 1) + ((TVD(j) - TVD(j - 1)) * (depth(i) - MD_array(j - 1)) / (MD_array(j) - MD_array(j - 1)))
Cells(3 + i, 16).Value = TVD_2
Else
j = j
End If
Next j
Next i


End Sub

[Pan314]

Hi Betherzy!

In connexion with functions I Know , what it means to interpolation.
Now you mention it in relation to number string interpolation.
This Is unknown to me. I do not know how the long number series will be a short.
Explain otherwise, it will be perhaps understandable.


Thanks

Pan314

[apo]

Can you attach a Workbook showing some raw data and also your desired result..

[betherzy]

Hey all thanks for the feedback, attached is a sample of the book and the code Ive wrote so far, i get a 'subscript out of range' error.

essentially, I want to take the "Depth" column and place it within ranges in the "Measured depth" column so I may interpolate what the "TVD" of that specific depth would be. Any help would be much appreciated.

Thanks

[betherzy]

what it means to interpolation.
Pan314

Pan, its just the opposite of extrapolation, so you want to take an approximation between two bounds. I don't think that really is the issue for this, I just need a way to stick a longer data set in between moving bounds of a smaller data set. does that make any sense?

[gmr4evr1]

betherzy,

I have been working with the codes you posted (the original from yesterday, and the updated one from today). With some changes to the code, I can get it to run without errors, but, I don't see where it is doing anything.

[betherzy]

Hey guys, figured it out, thanks a bunch, attached final if anyone is interested

[betherzy]

The only problem is it runs a few past the last row, so will error out unless told to skip, any ideas on how to fix this one?

[gmr4evr1]

Nice job!!

[gmr4evr1]

Hmm, it doesn't error when I run it...what is the error?

[betherzy]

Thanks! it doesn't error, just has a couple rows of nonsense numbers afterward I guess

[gmr4evr1]

Ahh, ok. I do see where it is going more rows than needed, but haven't figured out why......yet.