CODEWITHSUNDEEP
vbaexcel
Inf
Inf

Reputation: 21

Finding closest coordinates with the help of VBA

My goal is to find the closest point to a certain point. I have a list of coordinates to different points and I want to find the closest to a certain point (Point1) that is not in that list.

With the MIN function I can find the closest distance between Point1 and the closest point from the list.

The problem is that I can't find the coordinates(or at least one coordinate) of that point. For this I think I need to use VBA.

I would use VBA to find one coordinate of the point I'm looking for (the point with the closest distance) in the column of one coordinate (for example x). I've written an easy code that should give me what I want if I add my if condition but it doesn't work. The if condition I will be using in the code would be:

If columnx = (SQRT(Abs((distance) ^ 2 - ((columny - pt1y) ^ 2))) + pt1x) Then pt2x = columnx

pt2x would be the function name, pt1y-y coordinate of Point1, pt1x - x coordinate of Point1, the rest you should understand.

The code I have written is:

Function K23S(l As Range, s As Range) As Variant
Dim K() As Variant
K = Range("l").Value
Dim M() As Variant
M = Range("s").Value
Dim i As Long
Dim j As Long
    For i = LBound(K) To UBound(K)
    For j = LBound(M) To UBound(M)
        If K(i) = M(j) Then
            p = K(i)
        End If
    Next j
    Next i
p = K23S
End Function

It compares two columns (for example A1:A32 and B1:B32) and should give one's value if the values in the same row match (if they start on the same row). At the moment it gives #VALUE! error. I have tried the For Each as well but the result is the same. Values in the cells are in the right format.

What am I doing wrong? Or maybe you can offer a different solution?

I've used this link, maybe it helps to undestand: http://excelmacromastery.com/Blog/index.php/the-complete-guide-to-using-arrays-in-excel-vba/

*The Excel formulas found on the internet are not giving the right answers in some cases so I would like to try a different approach.

Upvotes: 2

Views: 2076

Answers (2)

Inf
Inf

Reputation: 21

I just solved my problem. The column nr was supposed to be aaded to the arrays. The correct code would be:

Function K23S(l As Range, s As Range) As Variant

Dim K() As Variant
Dim M() As Variant

K = l.Value
M = s.Value

Dim i As Long
Dim j As Long
For i = LBound(K) To UBound(K)
    For j = LBound(M) To UBound(M)
       If K(i, 1) = M(j, 1) Then
            K23S = K(i, 1)
            Exit Function
        End If
    Next j
Next i
K23S = "Default Value"

End Function

Upvotes: 0

enderland
enderland

Reputation: 14145

p = K23S should be K23S = p.

Add Option Explicit to the top of your module, too, as it should identify compile errors like this.

Also, you are incorrectly doing your range assignments.

Function K23S(l As Range, s As Range) As Variant
    Dim K() As Variant
    Dim M() As Variant

    K = l.Value
    M = s.Value

    Dim i As Long
    Dim j As Long
    For i = LBound(K) To UBound(K)
        For j = LBound(M) To UBound(M)
           If K(i) = M(j) Then
                K23S = K(i)
                Exit Function
            End If
        Next j
    Next i
    K23S = "Default Value"
End Function

I also added a early exit on your function when it finds the match and a default return value (in case it doesn't find anything).

Upvotes: 1

Related Questions