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?
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
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
.
your array method works well with my code, thanks alot. Anyways can i ask you some basic question like why with application is needed when using array? I tried to use array just now without the application and it does not works until i seen your post and then i realized it is needed.
Lets say If .Count(.Match(Array("Voltage", "Time"), arr, 0)) = 3 or =4, does it still means both values are still present within my initial range?
That will never happen. You are comparing an array of two values against
arr
. So either the return is 0, 1 or 2. AndApplication
is needed since we basically using two worksheetfunctions, but theApplication
ensures that it wouldn't give you a run-time error.oh ya can i also ask what does the 0 here means in Count(.Match(Array("Voltage", "Time"), arr, 0)) ?
@cena, that's the match_type parameter of the
MATCH
function which determines that we only want exact matches.