PDA

View Full Version : Several VBA questions / index match



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

prometheus
04-21-2016, 07:50 AM
Ok i noticed i can replace the index match with a simple vlookup :

Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("ai:aw"), 12, False)

this seems to work fine


the only remaining issue is to nest this in an if then loop..


because the value im trying to lookup is situated in two different columns ( AI or AJ) the formula above works fine when the value is indeed found in column AI, but when it isnt , it should instead perform the lookup in the range (AJ:AW)

so far im unable to piece this together