Como implementar as funções MATCH e INDEX juntas no Excel VBA?

Question

Eu estou tentando encontrar o número da linha da primeira célula que é menor que zero. Então, ao navegar pela rede, consegui criar essa fórmula que funciona perfeitamente bem:

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

Mas eu gostaria de implementar isso no Excel VBA e eu tentei algumas opções e recebo erro de incompatibilidade de tipo 13, pode alguém me ajudar nisso?

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

Obrigado antecipadamente.

    

Answer 1

Tanto quanto eu entendo, você está usando o operador de interseção (espaço) em sua fórmula INDEX: "$ C2531: $ C2731 < 1". Isso irá comparar cada célula entre um intervalo ($ C2531 $ C2731) contra outro intervalo, mas, neste caso, uma comparação "< 1". Assim, a Intersecção retornará um intervalo contendo um valor VERDADEIRO ou FALSO para cada comparação (isto é, uma fórmula de matriz). A MATCH, em seguida, examinará esse intervalo resultante para valores "VERDADEIROS".

Em outras palavras, a fórmula é relativamente complexa nos bastidores, e embora seja curta para ler, no VBA você quer manter as coisas tão simples e claras quanto possível do ponto de vista "por trás das cenas" - ou você correr em problemas. O que você está tentando alcançar é simples, mas não é necessário integrar as funções INDEX e MATCH no VBA.

No Visual Basic, não há operador de interseção nativa. Pelo que eu posso encontrar, o único equivalente é a função Application.Intersect, que pega objetos Range como parâmetros. Infelizmente, embora seja possível criar o primeiro intervalo como "Set myRange = Range (" $ C2531: $ C2731 "), não consegui criar um objeto Range de" < 1 "no VBA, pois isso não é válido Portanto, parece que o operador de interseção em fórmulas do Excel é mais capaz / flexível do que o método Application.Intersect no VBA. E esse é o ponto de falha aqui ("$ C2531: $ C2731 < 1" não é válido VBA)

Agora você pode passar um dia tentando ver se consegue cruzar um intervalo e um "< 1" no VBA, mas há maneiras mais simples de fazer isso no VBA. Se fosse eu, eu não usaria funções de planilha, e apenas usaria um loop For ... Next para passar por cima de suas linhas e fazer a comparação em um basic visual básico para cada célula em seu Range. Se uma comparação corresponder, retorne o número da linha.

Para ter uma ideia, seria algo como:

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

    

Answer 2

Você pode simplesmente enviar o forumla como uma string. Abaixo está um exemplo.

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