Vba code to find value in excel dataset

[bendy315]

I have a (x,y) table of data in excel that I am trying to find a corresponding z value for. Here is an example of the dataset in excel:

<table x:str="" style="border-collapse: collapse; width: 528pt;" border="0" cellpadding="0" cellspacing="0" width="704"><col style="width: 48pt;" span="11" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt; width: 48pt;" width="64" height="17">Nominal</td> <td class="xl28" style="width: 48pt;" width="64">Outer</td> <td class="xl29" style="width: 48pt;" width="64">
</td> <td class="xl29" style="width: 48pt;" width="64">
</td> <td class="xl29" style="width: 48pt;" width="64">
</td> <td class="xl29" style="width: 48pt;" width="64">
</td> <td class="xl29" style="width: 48pt;" width="64">
</td> <td class="xl29" style="width: 48pt;" width="64">
</td> <td class="xl29" colspan="3" style="width: 144pt;" width="192">PROCESS TEMPERATURE, °F</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">Size</td> <td class="xl24">Diameter</td> <td class="xl25" x:num="">100</td> <td class="xl25" x:num="">150</td> <td class="xl25" x:num="">200</td> <td class="xl25" x:num="">250</td> <td class="xl25" x:num="">300</td> <td class="xl25" x:num="">350</td> <td class="xl25" x:num="">400</td> <td class="xl25" x:num="">450</td> <td class="xl25" x:num="">500</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt;" x:num="0.5" height="17"> 1/2</td> <td class="xl28" x:num="">0.84</td> <td class="xl30" x:num="6.2249865283722672">6.2</td> <td class="xl30" x:num="12.97481474502964">13.0</td> <td class="xl30" x:num="20.150672976256804">20.2</td> <td class="xl30" x:num="27.736257060164679">27.7</td> <td class="xl30" x:num="35.724732658576173">35.7</td> <td class="xl30" x:num="44.111398977109644">44.1</td> <td class="xl30" x:num="52.894413672014863">52.9</td> <td class="xl30" x:num="62.070696853825481">62.1</td> <td class="xl30" x:num="71.639543183421409">71.6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" x:num="1" height="17">1 </td> <td class="xl28" x:num="1.3149999999999999">1.315</td> <td class="xl30" x:num="7.8621707065615372">7.9</td> <td class="xl30" x:num="16.407728325994952">16.4</td> <td class="xl30" x:num="25.497258793281468">25.5</td> <td class="xl30" x:num="35.107604968568204">35.1</td> <td class="xl30" x:num="45.228916779722297">45.2</td> <td class="xl30" x:num="55.855236268845886">55.9</td> <td class="xl30" x:num="66.981992056071263">67.0</td> <td class="xl30" x:num="78.607242088418189">78.6</td> <td class="xl30" x:num="90.728049373323287">90.7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" x:num="1.5" height="17">1 1/2</td> <td class="xl28" x:num="">1.9</td> <td class="xl30" x:num="9.7490196453732061">9.7</td> <td class="xl30" x:num="20.369246104283523">20.4</td> <td class="xl30" x:num="31.670241514700454">31.7</td> <td class="xl30" x:num="43.621376590465516">43.6</td> <td class="xl30" x:num="56.207937927415799">56.2</td> <td class="xl30" x:num="69.422349188915831">69.4</td> <td class="xl30" x:num="83.259105462501935">83.3</td> <td class="xl30" x:num="97.714064390959678">97.7</td> <td class="xl30" x:num="112.78383080368296">112.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" x:num="2" height="17">2 </td> <td class="xl28" x:num="2.375">2.375</td> <td class="xl30" x:num="11.226415688654985">11.2</td> <td class="xl30" x:num="23.472060232404321">23.5</td> <td class="xl30" x:num="36.507505811608745">36.5</td> <td class="xl30" x:num="50.293147971329191">50.3</td> <td class="xl30" x:num="64.813475345323553">64.8</td> <td class="xl30" x:num="80.057348008212429">80.1</td> <td class="xl30" x:num="96.018652111154822">96.0</td> <td class="xl30" x:num="112.69303627048382">112.7</td> <td class="xl30" x:num="130.07545120471761">130.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" x:num="2.5" height="17">2 1/2</td> <td class="xl28" x:num="2.875">2.875</td> <td class="xl30" x:num="12.748703847450978">12.7</td> <td class="xl30" x:num="26.671363808297226">26.7</td> <td class="xl30" x:num="41.495721393741285">41.5</td> <td class="xl30" x:num="57.175369595314088">57.2</td> <td class="xl30" x:num="73.689705839687377">73.7</td> <td class="xl30" x:num="91.02755686330876">91.0</td> <td class="xl30" x:num="109.18160641299471">109.2</td> <td class="xl30" x:num="128.14507650435451">128.1</td> <td class="xl30" x:num="147.91319664276679">147.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" x:num="3" height="17">3 </td> <td class="xl28" x:num="">3.5</td> <td class="xl30" x:num="14.618971504434734">14.6</td> <td class="xl30" x:num="30.605000161916326">30.6</td> <td class="xl30" x:num="47.630256570710664">47.6</td> <td class="xl30" x:num="65.639521939477078">65.6</td> <td class="xl30" x:num="84.60807210584197">84.6</td> <td class="xl30" x:num="104.52310808502594">104.5</td> <td class="xl30" x:num="125.3734302588025">125.4</td> <td class="xl30" x:num="147.15392679591966">147.2</td> <td class="xl30" x:num="169.8568163280888">169.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" x:num="4" height="17">4 </td> <td class="xl28" x:num="">4.5</td> <td class="xl30" x:num="17.562359328672006">17.6</td> <td class="xl30" x:num="36.797738317707235">36.8</td> <td class="xl30" x:num="57.290554448951852">57.3</td> <td class="xl30" x:num="78.970522642571396">79.0</td> <td class="xl30" x:num="101.80699335352166">101.8</td> <td class="xl30" x:num="125.78179862075608">125.8</td> <td class="xl30" x:num="150.88247848331432">150.9</td> <td class="xl30" x:num="177.10094813642692">177.1</td> <td class="xl30" x:num="168.60443186472855">168.6</td> </tr> </tbody></table>
so an example of the lookup could be for a process temperature of 225 and a diameter of 3.5, where I would want the corresponding lookup value to be 65.6. If it is not the exact value I want to take the next largest (same for the Diameter) so that the answer is always larger and on the conservative side.

So I originally tried using the Application.WorksheetFunction.VLookup in my macro.

HotPipeHL = Application.WorksheetFunction.VLookup(OD, Worksheets("Output_Tables").Range(t2Array), Application.WorksheetFunction.Match(HotPipeT, Worksheets("Output_Tables").Range(sRange)) + 1)

However, I am running through this code in a loop about 10,000 times, so it takes FOREVER for the macro to run when coded using this function.

I was told that the .Find function works much faster, but I haven't been able to get to work. Here's what I got so far...

With Worksheets("Output_Tables").Range(t2Array)
Set HotPipeHL = .Find(What:=OD, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, Application.WorksheetFunction.Match(HotPipeT, Worksheets("Output_Tables").Range(sRange)) - 1)
End With

Here I run into a problem if the OD is not an exact match, and also still have to use the Application.WorksheetFunction.Match function to find the corresponding process temperature and count the number of columns to offset. Is there a way to round up if the OD is not an exact match? and will the match function slow down the macro run time? Is there a faster way?

any help would be very appreciated! Thanks!

 

[ravishankar]

Hi
try these codes

Sub bendy()
Dim a As Long, b As Long, c As Long, d As Long
Dim e As Single
a = InputBox("enter diameter")
b = InputBox("enter temperature")
c = Application.WorksheetFunction.Match(a, Range("B1:B8"), 1) + 1
d = Application.WorksheetFunction.Match(b, Range("A1:K1"), 1) + 1
e = Round(Cells(c, d), 3)
MsgBox "The Z value for dia -  " & a & ",  temp. of  " & b & " C is " & e
End Sub

Ravi