Using VBA to find the closest match in selected cells

Hello everyone,

I'm trying to find the closest match of a given value (always in the same cell but the value change) in a selected range (changing according to the value of another cell).

Here's an example:

1- I ask for a Drive RPM (869 RPM)
2- A formula check for the closest equal or higher match (870RPM) in the entire range (Sheet2!J2:J564)
3- According to the RPM (870RPM) it gives me the matching HP per belt (1.53, 2.30, 3.05 & 3.79). In this case there is 4 options.
4- I would then like to have the correction factor based on the length of the belt (inputed in a cell) and the different HP per belt.


I need something similar to the following function but I would need to put variables instead of fixed cells:

=IF(CT18>MAX(Sheet2!J2:J564);"Error";INDEX(Sheet2!J2:J564;MATCH(LARGE(Sheet2!J2:J564;COUNTIF(Sheet2!J2:J564;">="&CT18));Sheet2!J2:J564;0)))



Something like that:

=IF(CT18>MAX(Sheet2!Cells(2,x):Cells(564,x);"Error";INDEX(Sheet2!Cells(2,x):Cells(564,x);MATCH(LARGE(Sheet2!Cells(2,x):Cells(564,x);COUNTIF(Sheet2!Cells(2,x):Cells(564,x);">="&CT18));Sheet2!Cells(2,x):Cells(564,x);0)))

With "x" as a variable previously defined



Thanks in advance

It seems to me that you're trying to combine a vba range reference construct (Cells(2,x)) with a formula construct, or vice-versa, and I could be wrong, but I don't think it can be done. Have you considered utilizing the INDIRECT function? Try entering "Sheet2!J2:J564" into cell A1. Then in your formula, replace that value with "INDIRECT(A1)". I haven't tried it with your extensive formula, but it's worth a shot.

Hi natefarm,

I tried what you told me but it didn't work. The INDIRECT function was not refereing to the cell, it stayed "INDIRECT(A1)" in the formula... I'm attaching my excel file so you can take a look. What I'm trying to do is:

-Based on the Desired DriveN RPM entered manually (CT18);
-Rounded up to the next matching value (CR19) (range for the round up on sheet 2 based on the Motor RPM (CT10) and the chosen cross section (CT17));
-With the rounded up DriveN RPM (CT19), I find the HP per Belt matching the RPM value (CT22, CV22, CX22 & CZ22)
-I now need to find the value over and under the desired nominal center distance (CT21) for each HP per belt founded previously

Thanks

It seems to work slick as a whistle for me. Unhide column CR. In CR18, type Sheet2!J2:J564, or to make it easer to edit and more flexible, just type Sheet2!J:J.
Name the cell IND (or something better -- I don't know what column J represents).
Then in CR19 (eventually CT19), enter:

Formula:

=IF(CT18>MAX(INDIRECT(IND)),"Error",INDEX(INDIRECT(IND),MATCH(LARGE(INDIRECT(IND),COUNTIF(INDIRECT(IND),">="&CT18)),INDIRECT(IND),0)))

Changing the IND range, or the DriveN RPM produces the new results.

Merry Christmas!

Cool, thanks.

I kept working on it today and I finally did it with a for/if loop and it's also working good (even tough it's a bit longer than what you gave me...).

For i = 13 To 31
                        If Sheets("Sheet2").Cells(strRowNoList, i) = "N/A" Then
                        ElseIf Sheets("Sheet2").Cells(strRowNoList, i) <= Sheets("Sheet1").Cells(21, "CT") Then
                            min_dist = Sheets("Sheet2").Cells(strRowNoList, i).Value
                        Else
                            Cells(23, col) = min_dist
                            Cells(23, col + 1) = Sheets("Sheet2").Cells(strRowNoList, i)
                            Cells(24, col) = Sheets("Sheet2").Cells(strRowNoList, "B")
                            Cells(25, col) = Sheets("Sheet2").Cells(strRowNoList, "C")
                            col = col + 2
                            Exit For
                        End If
                    Next i

Thanks again!!