Warm tip: This article is reproduced from stackoverflow.com, please click
excel vba

how to find multiple strings using range.value?

发布于 2020-03-27 15:39:41

i tried to use range("A1:I1").value to find multiple strings at the first row however it shows that error "mismatch". What have i done wrong here? Is there another way to do it?

enter image description here

Dim sht as worksheet

Set sht = ThisWorkbook.Sheets("Result")

If sht.range("A1:I1").value = " Voltage" and sht.range("A1:I1").value = " Time"     ,<---------error 'mismatch' occurs here  

call powerandtime
Questioner
cena
Viewed
13
JvdV 2020-01-31 16:30

The problem here is that you are comparing an array of values against a single value. In case of such a small array you can make use of some Application.Methods. Another option would be to use Range.Find on the actual Range object. I'll demonstrate both below:


Application.Methods

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Result")
Dim arr As Variant

With Application
    arr = .Transpose(ws.Range("A1:I1").Value)
    If .Count(.Match(Array("Voltage", "Time"), arr, 0)) = 2 Then
        Call PowerAndTime
    End If
End With

End Sub

What happens here is that .Match will return an array of two elements. It will either return an error value to the array if either "voltage" or "time" is not found, or it would return a numeric value when either one of them is found. Then .Count will count numeric values within that returned array, and only if the count would be 2, is when both values are present within your initial range.

Note: .Match needs a 1D-array, hence the .Transpose at the start.


Range.Find

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Result")
Dim rng1 As Range, rng2 As Range

Set rng1 = ws.Range("A1:I1").Find("Voltage", lookat:=xlWhole)
Set rng2 = ws.Range("A1:I1").Find("Time", lookat:=xlWhole)

If Not rng1 Is Nothing And Not rng2 Is Nothing Then
    Call PowerAndTime
End If

End Sub

So only when both "Voltage" and "Time" are found as xlWhole values within your specific range, it would continue to call PowerAndTime.