# Thread: vba for a range and return value Thanks:  1 Post #5343620 (1) Likes:  1 Post #5343620 (1)

1. ## Re: vba for a range and return value

this code is shows you how to do a MATCH in VBA:
Code:
```marr = Range("NameManager1")
farr = Range("Namemanager")
For kk = LBound(marr, 1) To UBound(marr, 1)
If marr(kk, 1) = farr Then
MsgBox kk
Exit For
End If
Next kk```

2. ## Re: vba for a range and return value

so A1 is called in "Smith" and my range from F1:P1 is called Tom.
My cells in my range of C column will fill one by one "Smith" .
so i have these two formulas =MATCH(Smith;Tom;0) which is in AA20 cell and the formula =AA20+COUNTIF(Tom;Smith)-1.
so if in column B we have a Yes the in column D put the result for the match fuction and in E the result of the countif

3. ## Re: vba for a range and return value

I think I have understood your requirements, if so this should do it:
Code:
```lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 3))
outarr = Range(Cells(1, 4), Cells(lastrow, 4))
tomrange = Range("Tom")

For i = 1 To lastrow
If inarr(i, 1) = "YES" Then
A1 = inarr(i, 2)
matchfnd = False
matchi = 0
matchcnt = 0
For kk = LBound(tomrange, 2) To UBound(tomrange, 2)

If tomrange(1, kk) = A1 Then
If Not (matchfnd) Then
matchfnd = True
matchi = k
End If
matchcnt = matchcnt + 1

End If
Next kk
outarr(i, 1) = matchi + matchcnt - 1
End If
Next i
Range(Cells(1, 4), Cells(lastrow, 4)) = outarr```

4. ## Re: vba for a range and return value

I just spotted a typo in the code "k" should be "kk"
try this:
Code:
```Sub test()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 3))
outarr = Range(Cells(1, 4), Cells(lastrow, 4))
tomrange = Range("Tom")

For i = 1 To lastrow
If inarr(i, 1) = "YES" Then
A1 = inarr(i, 2)
matchfnd = False
matchi = 0
matchcnt = 0
For kk = LBound(tomrange, 2) To UBound(tomrange, 2)

If tomrange(1, kk) = A1 Then
If Not (matchfnd) Then
matchfnd = True
matchi = kk
End If
matchcnt = matchcnt + 1

End If
Next kk
outarr(i, 1) = matchi + matchcnt - 1
End If
Next i
Range(Cells(1, 4), Cells(lastrow, 4)) = outarr

End Sub```

5. ## Re: vba for a range and return value

Hi,
Sorry for the late response.
yes it about what i want but up till now i think the best solution was
Sub test1()

lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 3))
outarr = Range(Cells(1, 4), Cells(lastrow, 4))
For i = 1 To lastrow
If inarr(i, 1) = "Yes" Then
Cells(1, 1) = inarr(i, 2)
outarr(i, 1) = Cells(4, 1)
End If
Next i
Range(Cells(1, 4), Cells(lastrow, 4)) = outarr

End Sub