if iserror match not functioning

[prometheus]

hi

wrote below code

overal structure is : test if a certain value exists in range AI ---- i wrote .range("ai:aw") because ("ai') doenst seem to work ? how do i correctly reference one column? .columns("ai") ?

so it should check if it finds the value in that specific column if not do A, else do B

somehow both A and B seperately work, but the combination doenst.. any tips?

edit, when i run the code it looks like it either executes A for the full list or B for the full list, while it should infact evaluate per line to do A or B...




If IsError(Application.Match(Cells(21 + i, 31), Sheets("data").Range("ai:aw"), 0)) Then
Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("ai:aw"), 11, False)
i = i + 1
Loop
Else
Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("aj:aw"), 12, False)
i = i + 1
Loop
End If
End Sub

[Leith Ross]

Hello prometheus,

This should work. It assumes the cells in the Do loops are on the ActiveSheet.


Dim rngData As Range
Dim rngLookup As Range
Dim Wks As Worksheet

Set Wks = Worksheets("Data")

Set rngData = Wks.Range("A1").CurrentRegion
Set rngLookup = Intersect(rngData, rngData.Columns("AI"))

If rngLookup Is Nothing Then Exit Sub

If IsError(Application.Match(Cells(21 + i, 31), rngLookup, 0)) Then

Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), rngData, 11, False)

i = i + 1
Loop

Else

Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), rngData, 12, False)

i = i + 1
Loop

End If

[prometheus]

Hi

thanks for the input
im afraid it will not work , i discovered the problem is more complex

probably my whole approach was wrong

i have data like this :

Week HomeTeam Awayteam Helpcolumnhometeam Helpcolumnawayteam HelpHT-1 HelpAT-1
1 arsenal manchester arsenal1 manchester1 arsenal0 manchester0
2 tottenham arsenal tottenham2 arsenal2 tottenham1 arsenal1
3 arsenal chelsea arsenal3 chelsea3 arsenal2 chelsea2

-- I made 4 helpercolumns because concatenating in VBA is too difficult for me ;)

what i try to do is the following

take week 3 : arsenal is playing home , i want to lookup the old arsenal rating in a range ai:aw , so i need to check if arsenal2 exists either in column helpht-1 ( if their previous match was a home match) or helptat-1 if their previous match is an away match-- so these two columns are basically concatenate Week-1&"hometeam" (note sometimes teams havent played the week before so we need to check -2, but this is complexity i cannot even grasp right now ;) --
if im able to find the column i start a vlookup either with starting range Helpht-1 or starting range helpAT-1


the vlookup part is covered with this formula :
'Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("ai:aw"), 11, False)