prometheus
04-21-2016, 07:03 AM
hi all,
im building a sheet where i try to implement an index match formula
the working formula in excel is :
=INDEX(AT:AW;MATCH(AE17;AJ:AJ;0);4)
what im trying to build in vba is the following :
validate if the match part has an error or not (MATCH(AE17;AJ:AJ;0);4))
if no error = =INDEX(AT:AW;MATCH(AE17;AJ:AJ;0);4)
if an error : =INDEX(AT:AW;MATCH(AE17;Ai:Ai;0);3)
---- my current code ( im also looping this to go through several cells)
Sub oldhome_home()
Dim i As Integer
i = 1
If IsError(Application.Match((Cells(21 + i, 31)), Range("aj:aj"), 0)) Then
Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = Application.WorksheetFunction.Index((at:aw), Application.WorksheetFunction.Match((Cells(21 + i, 31)), (aj:aj), 0), 4)))
i = i + 1
Loop
Else
Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = 5
i = i + 1
Loop
End If
End Sub
im building a sheet where i try to implement an index match formula
the working formula in excel is :
=INDEX(AT:AW;MATCH(AE17;AJ:AJ;0);4)
what im trying to build in vba is the following :
validate if the match part has an error or not (MATCH(AE17;AJ:AJ;0);4))
if no error = =INDEX(AT:AW;MATCH(AE17;AJ:AJ;0);4)
if an error : =INDEX(AT:AW;MATCH(AE17;Ai:Ai;0);3)
---- my current code ( im also looping this to go through several cells)
Sub oldhome_home()
Dim i As Integer
i = 1
If IsError(Application.Match((Cells(21 + i, 31)), Range("aj:aj"), 0)) Then
Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = Application.WorksheetFunction.Index((at:aw), Application.WorksheetFunction.Match((Cells(21 + i, 31)), (aj:aj), 0), 4)))
i = i + 1
Loop
Else
Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = 5
i = i + 1
Loop
End If
End Sub