Jak zaimplementować funkcje MATCH i INDEX w Excel VBA?

3

Próbuję znaleźć numer wiersza pierwszej komórki, który jest mniejszy od zera. Przeglądając sieć, udało mi się opracować tę formułę, która działa doskonale:

=MATCH(TRUE,INDEX($C2531:$C2731 < 1,0),0)

Ale chciałbym zaimplementować to w Excelu VBA i próbowałem kilka opcji i otrzymuję błąd niezgodności typu 13, Czy ktoś może mi pomóc w tym?

With Application.WorksheetFunction
    Range("C2527").Value = .Match(True, .Index(Range("C2531:C2731") < 1, 0), 0)
End With

Z góry dziękuję.

— NK1
źródło

2

O ile rozumiem, używasz operatora przecięcia (spacji) w formule INDEX: „$ C2531: $ C2731 <1”. Spowoduje to porównanie każdej komórki z jednego zakresu (C2531 $ C2731) z innym zakresem, ale w tym przypadku porównanie „<1”. Więc Przecięcie zwróci zakres zawierający wartość PRAWDA lub FAŁSZ dla każdego porównania (tj. Formuła tablicowa). MATCH będzie następnie sprawdzał wynikowy zakres wartości „PRAWDA”.

Innymi słowy, formuła jest relatywnie złożona za kulisami i chociaż jest krótka do odczytania, w języku VBA chcesz zachować rzeczy tak proste i jasne, jak to możliwe z perspektywy „za kulisami” - lub napotkasz problemy . To, co starasz się osiągnąć, jest proste, ale nie można wcisnąć w VBA funkcji INDEX i MATCH.

W języku Visual Basic nie ma rodzimego operatora przecięcia. Z tego, co mogę znaleźć, jedynym odpowiednikiem jest funkcja Application.Intersect, która przyjmuje obiekty Range jako parametry. Niestety, chociaż pierwszy zakres można utworzyć jako „Ustaw myRange = Zakres („ $ C2531: $ C2731 ”), nie byłem w stanie utworzyć obiektu Zakres„ & lt; 1 ”w języku VBA, ponieważ nie jest to prawidłowy Wydaje się, że operator przecięcia w formułach Excela jest bardziej zdolny / elastyczny niż metoda Application.Intersect w VBA, a to jest punkt błędu tutaj („C2531: $ 2731 $ <1” nie jest prawidłowym VBA)

Teraz możesz spędzić dzień próbując sprawdzić, czy uda ci się przeciąć zakres i „<1” w języku VBA, ale istnieją prostsze sposoby, aby to zrobić w VBA. Gdybym to był ja, nie używałbym funkcji Arkusz i używałbym standardowej pętli For ... Next, aby przejść przez twoje wiersze i wykonać porównanie wizualnie podstawowe dla każdej komórki w Twoim Zasięg. Jeśli jedno porównanie się zgadza, zwróć numer wiersza.

Aby uzyskać pomysł, wyglądałoby to tak:

Dim myRange As Range
Set myRange = Range("$C2531:$C2731")
For y = 1 to myRange.Cells.Rows.Count
   If y < 0 Then 
       result = myRange.Cells(y,1).Row
   End
Next y

— mtone
źródło

1

Dzięki mtone zrozumiałem złożoność funkcji. Użyłem więc prostej pętli FOR NEXT i mogłem uzyskać odpowiedź, której szukałem. Zmodyfikowany kod to Dim Range1 As Range Set Range1 = Range („$ C2531: $ C2731”) Dla Y = 1 do Range1.Cells.Rows.Count I = Range („C2531”). Offset (Y - 1, 0) .Value Jeśli I & lt; 1 Potem Rownum = Y GoTo Loop1end: End If Next Y Loop1end:

— NK1

1

Możesz po prostu wysłać formułę jako ciąg. Poniżej znajduje się przykład.

Range("C2527").Value = "=MATCH(TRUE,INDEX($C2531:$C2731 < 1,0),0)"

— Santosh
źródło

Dzięki Santosh, to była funkcja, którą napisałem, ale szukałem pomocy w reprezentowaniu tej funkcjonalności w VBA.

— NK1