I am trying to write code that essentially performs linear interpolation for me and spits the result out into an email. I have set both the first column and top row as their own ranges (excluding 3300). Each require a user input of a number within each of the ranges. The interpolation is being performed against the top row. Currently I am using the Excel Lookup Worksheet Function to find the x1 for interpolation. My plan is to then use the address/cell location of x1 to get the remaining interpolation values required (x2, y1, y2) but I can't figure out how to define its location when its found with a variable.
Attached is a snippet of the table:
I am attaching just the address related components of the code currently but if needed I can copy paste the whole function, thanks!
Dim avgSourceActivity As Double, ContainerActivity As Double, NumbSources As Integer
NumbSources = ws1.Range("D9").Value
'defining search ranges and variable locations
Dim avgSourceActRange As Range
Set avgSourceActRange = ws2.Range("h5:af5")
Dim numbSourcesRange As Range
Set numbSourcesRange = ws2.Range("G6:G19")
ws1.Range("D12").Value = avgSourceActivity
'searches the first column for the value of NumbSources
Dim FoundSourceNumb As Range
Set FoundSourceNumb = Range(numbSourcesRange).Find(NumbSources)
'establishes bounds of source activity for linear interpolation
Dim LookupFormulaMin As Double, LookupFormulaMax As Double
LookupFormulaMin = WorksheetFunction.Lookup(avgSourceActivity, avgSourceActRange)
MsgBox (LookupFormulaMin)
'write something in here to define LookUpFormulaMin's cell location
'use it to get the x2 by shifting over one in the cell range
LookupFormulaMax = ws2.Cells(LookupFormulaMin.Row, LookupFormulaMin.Column + 1).Value
MsgBox (LookupFormulaMax)