Hi there,
I am trying to use a match function in VBA to return a value when a number of values in a row match a certain criteria (I have tried the Find approach on the Ozgrid help pages but it is not what I am after). My problem using a match function of course is that when there is no match the code returns a type mismatch error. I have tried to circumvent the error problem using an "On error resume next" but this then delivers a match for all rows. Anyone have any thoughts about how I can get around this problem?
Code
Sub DataSum()
Dim strData As String
Dim i As Integer
i = 0
Sheets("Sheet1").Activate
Range("B4", Range("B4").End(xlDown).Offset(0, 0)).Select
For Each cell In Selection
If cell.Value = 0 Then
i = i + 1
Else
strData = Sheets("Sheet1").Range("$B$4").Offset(i, 0)
myVal1 = Application.Match("A", Range("$C$4").Offset(i, 0), 0)
myVal2 = Application.Match("B", Range("$F$4").Offset(i, 0), 0)
myVal3 = Application.Match("C", Range("$I$4").Offset(i, 0), 0)
myTotal = myVal1 * myVal2 * myVal3
If myTotal = 1 Then
Sheets("Sheet1").Range("K2").Offset(i, 0) = strData
Else
End If
i = i + 1
End If
Next cell
End Sub
Display More